Blog

Tagged by 'sqldatasource'

  • Published on
    -
    1 min read

    Programmatically Using SqlDataSource

    The SqlDataSource control enables you to use a Web control to access data located in a relational data base, including Microsoft SQL Server and Oracle databases, as well as OLE DB and ODBC data sources. You can use the SqlDataSource control with other controls that display data, such as the GridView, FormView, and DetailsView controls, to display and manipulate data on an ASP.NET Web page, using little or no code.

    protected void PopulateComments(string recordNo)
    {
        //Connection to database
        string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        
        SqlDataSource sqlDataSource = 
            new SqlDataSource(connString, "SELECT [CommComments], [RelComments], [ManageComments], [RoleComments], [PersonalComments] FROM [tblSurveyResults] WHERE ([SurveyID] = @SurveyID)");
    
        sqlDataSource.SelectParameters.Add(new Parameter("SurveyID", System.TypeCode.Int32, recordNo));
        
        System.Data.DataView dv = (System.Data.DataView)sqlDataSource.Select(DataSourceSelectArguments.Empty);
        
        //Output Comments to label controls
        lblQuestion1Comments.Text = dv[0][0].ToString();
        lblQuestion2Comments.Text = dv[0][1].ToString();
        lblQuestion3Comments.Text = dv[0][2].ToString();
        lblQuestion5Comments.Text = dv[0][3].ToString();
        lblQuestion4Comments.Text = dv[0][4].ToString();
    }
     
    

    As you can see, the data is output from the database in little code. I believe this is far more flexible compared to dragging a dropping a SqlDataSource onto the page. The above code queries the database and then passes the selected fields to a DataView. You can then output each individual column data where you want. In this case, I have output the data to labels:

    //Output Comments to label controls
    lblQuestion1Comments.Text = dv[0][0].ToString(); //Output CommComments column data
    lblQuestion2Comments.Text = dv[0][1].ToString(); //Output RelComments column data
    lblQuestion3Comments.Text = dv[0][2].ToString(); //Output ManageComments column data
    lblQuestion4Comments.Text = dv[0][3].ToString(); //Output RoleComments column data
    lblQuestion5Comments.Text = dv[0][4].ToString(); //Output PersonalComments column data