This Tutorial is not for those who need to plugin the sqldatasource in the front end, but rather how to use the SqlDataSource in the backend codes.
There are tons of tutorial of how to write SqlDataSource for Gridview, DataList, Repeater and other objects in the front end (aspx file), but not many tutorials out there on how to simplify things for using SqlDataSource in the backend (aspx.cs file).
Example:
you have a gridview called “gvStudents”, and you have a sql query “SELECT * FROM Students”. you want to bind the query to the gridview but you want to do it in the backend. How do you do it?
here is how you do it.
SqlDataSource SDSStudents = new SqlDataSource();
SDSStudents.SelectCommand = “SELECT * FROM Students”;
SDSStudents.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnectStringName"].ConnectionString;
gvStudents.DataSource = SDSStudents;
gvStudents.DataBind();
5 Simple Steps
1) Create a new SqlDataSource object
2) Assign the SQL Query to the Select Command
3) Assign the ConnectionString to the SqlDataSource ConnectionString
4) Assign the SqlDataSource to the gridview DataSource
5) DataBind the gridview
I know your next question is “What about Stored Procedure? Can I do the same thing with a stored procedure?”
The answer is Yes, you can do the same thing with a stored procedure. here is how
SqlDataSource SDSStudents = new SqlDataSource();
SDSStudents.SelectCommand = “Stored Procedure Name”;
SDSStudents.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
SDSStudents.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnectStringName"].ConnectionString;
gvStudents.DataSource = SDSStudents;
gvStudents.DataBind();
Instead of 5 Simple Steps, it is now 6 simple steps
6 Simple Steps
1) Create a new SqlDataSource object
2) Assign the SQL stored procedure name to the Select Command
3) Set the SqlCommandType to be a StoredProcedure
4) Assign the ConnectionString to the SqlDataSource ConnectionString
5) Assign the SqlDataSource to the gridview DataSource
6) DataBind the gridview
I bet your next answer is “What about SelectCommand Parameters? Can I Add Parameters to the SqlDataSource?”
*Updated*
Instead of writing another entry, I decided to continue with this entry and answer the question about the Parameters inside the SqlDataSource.
Yes you can add parameter in the SelectCommand, either the select command is sql query or stored procedure.
This is how you do it.
SqlDataSource SDSStudents = new SqlDataSource();
SDSStudents.SelectCommand = “SELECT * FROM Students WHERE ID=@ID”;
SDSStudents.SelectParameters.Add(“ID”, TypeCode.Int32, “1″);
SDSStudents.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnectStringName"].ConnectionString;
gvStudents.DataSource = SDSStudents;
gvStudents.DataBind();
If using with Stored Procedure use this,
SqlDataSource SDSStudents = new SqlDataSource();
SDSStudents.SelectCommand = “Stored Procedure Name”;
SDSStudents.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
SDSStudents.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnectStringName"].ConnectionString;
SDSStudents.SelectParameters.Clear();
SDSStudents.SelectParameters.Add("Param1", strParamVal1);
SDSStudents.SelectParameters.Add("Param2", strParamVal2);
if (SDSStudents.SelectParameters["Param1"].DefaultValue == null || SDSStudents.SelectParameters["Param1"].DefaultValue == "")
SDSStudents.SelectParameters["Param1"].DefaultValue = " ";
if (SDSStudents.SelectParameters["Param2"].DefaultValue == null || SDSStudents.SelectParameters["Param2"].DefaultValue == "")
SDSStudents.SelectParameters["Param2"].DefaultValue = " ";
gvStudents.DataSource = SDSStudents;
gvStudents.DataBind();
Instead of 5 Simple Steps for the sqlquery, it is now 6 simple steps
6 Simple Steps
1) Create a new SqlDataSource object
2) Assign the SQL Query to the Select Command
3) using SelectParamters.Add(“ParameterName”, DBType or TypeCode, “ParameterValue”) to add new parameter to the selectCommand
4) Assign the ConnectionString to the SqlDataSource ConnectionString
5) Assign the SqlDataSource to the gridview DataSource
6) DataBind the gridview
