How do I connect my website to a SQL Server database?

These controls DataSet, DataAdapter, DataTable, DataConnection, DataReader, DataSource, DataGrid, DataView ... oh so very confusing!

How are all these data controls related? How do I populate my form with data?
In: Tech.Info-Tech Asked by: Rock Star Mar 17, 2008 - 81 Months Ago.  Viewed 5386 times



 Sort Answers: DateScore  Helpfulness Votes   
Here are the steps to using ADO.NET 2.0 to connect to SQL Server, from ASP.NET Web Forms or Windows Forms:

0) Use the System.Data.SqlClient namespace in your application. (Or preface each object with System.Data.SqlClient)

1) SqlConnection - Build a connectionString to create a SqlConnection object. Setup the SQL Server database with Integrated security to save on hassles. (Beginners can create a SqlDataSource or SqlDataAdapter to encapsulate steps 1-3, with lots of help from Visual Studio 2005.)

2) SqlCommand - Run SQL query or a stored procedure statement. Try the query in SQL Management Studio.

3) DataTable - Fill your object with data using DataTable.Load() with SqlCommand.ExecuteReader().

2-3b) An alternative to DataTable is the DataSet, for working with multiple related DataTables. DataSet needs a SqlDataAdapter to load data. (Another option is a DataReader, faster but less flexible. The old ADO called it RecordSets.)

4) Start using the DataTable contents. You can bind it to a DataGrid using the DataSource property, or load a single Row to a TextBox. You're ready!

[Sample Code]
        //1
        string dbString = "Data Source=localhost\\sqlexpress;Initial Catalog=pubs;Integrated Security=True";
        SqlClient.SqlConnection sqlconnection = new SqlClient.SqlConnection(dbString);
        sqlconnection.Open();

        //2
        SqlCommand sqlcommand = new SqlCommand("select * from Client");
        sqlcommand.Connection = sqlconnection;

        //3b
        DataTable dt = new DataTable();
        dt.Load(sqlcommand.ExecuteReader());
        //3b
        SqlDataAdapter dadapter = new SqlDataAdapter();
        dadapter.SelectCommand = new SqlCommand(("select * from Client", sqlconnection);
        DataSet dset = new DataSet(); // can use DataTable too
        dadapter.Fill(dset);

        //4
        GridView1.DataSource = dset;
        GridView1.DataBind();
        sqlconnection.Close();
Answered by: sherwin - 71 Months Ago.
1 Comments
‚óŹThis is fantastic article provide useful knowledge and i wish you share future article is also knowledgeable. thanks to this post. Computer Tips
Commented by: jaime123 - 27 Months Ago.
 Add Comments 
Here's a quick example on how to populate a GridView using a SqlDataSource in ASP.NET

    <asp:GridView CellPadding=4 PageSize=35 HorizontalAlign=Justify ID="GridView1"
        runat="server" AllowPaging="True" AllowSorting="True"
        AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
        <Columns>
            <asp:HyperLinkField DataTextField="LastName" HeaderText="Last Name"
                 SortExpression="pop" DataNavigateUrlFields="LastName"
                 DataNavigateUrlFormatString="Account.aspx?cid={0}" />
        </Columns>
        <PagerSettings Mode="NumericFirstLast" PageButtonCount="20" />
        <PagerStyle HorizontalAlign="Center" />
    </asp:GridView>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
        SelectCommand="select * from Customers">
    </asp:SqlDataSource>
Answered by: shshao - 71 Months Ago.
 Add Comments 

How do I connect my website to a SQL Server database? These controls DataSet, DataAdapter, DataTable, DataConnection, DataReader, DataSource, DataGrid, DataView ... oh so very confusing! How are all these data controls related? How do I populate my form with data? - HelpGlobe.Tech.Info-Tech
 
About Legal Copyright 2007- Helpglobe.com Invite Contact