Wednesday 18 July 2012

Beginning .Net : Fill DataTable from sql query using Data Adapter with C# Examples and VB.Net Examples

This is very useful post for .Net Beginners. Every .Net Developer use this in their application.
We can fill data table using SqlDataAdapter by execute sql query.
SqlDataAdapter is also use for execute standard INSERT, UPDATE, and DELETE statements.
SelectCommand , InsertCommand , UpdateCommand and DeleteCommand are the some very useful properties of SqlDataAdapter.
SqlDataAdapter is use for fill disconnected data table.
This is an ADO.Net .

Here is example.
In this example we execute select query of product_master table to get all records and fill in data table.

ASPX Code :
 <asp:GridView ID="gvProducts" runat="server">
 </asp:GridView>


C# Examples :
        //Create Data Table object, That hold records
        DataTable objTable = new DataTable();

        SqlConnection objConn = new SqlConnection();
        objConn.ConnectionString = @"Data Source=.\SQLEXPRESS;" +
                                   "Initial Catalog=TempDatabase;" +
                                   "User ID=sa;Password=sa;";  

        SqlCommand objcmd = new SqlCommand();
        objcmd.CommandText = "SELECT  * FROM product_master";
        objcmd.CommandType = CommandType.Text;
        objcmd.Connection = objConn;

        //Create Data Adapter object
        SqlDataAdapter objAdapt = new SqlDataAdapter();
        objAdapt.SelectCommand = objcmd;

        //Fill Data Table with return result of sql query
        objAdapt.Fill(objTable);

        gvProducts.DataSource = objTable.DefaultView;
        gvProducts.DataBind();

        objAdapt.Dispose();
        objcmd.Dispose();
        objConn.Dispose();

VB.net Example :
        'Create Data Table object, That hold records
        Dim objTable As New DataTable()

        Dim objConn As New SqlConnection()
        objConn.ConnectionString = "Data Source=.\SQLEXPRESS;" & _
                                    "Initial Catalog=TempDatabase;" & _
                                    "User ID=sa;Password=sa;"

        Dim objcmd As New SqlCommand()
        objcmd.CommandText = "SELECT  * FROM product_master"
        objcmd.CommandType = CommandType.Text
        objcmd.Connection = objConn

        'Create Data Adapter object
        Dim objAdapt As New SqlDataAdapter()
        objAdapt.SelectCommand = objcmd

        'Fill Data Table with return result of sql query
        objAdapt.Fill(objTable)

        gvProducts.DataSource = objTable.DefaultView
        gvProducts.DataBind()

        objAdapt.Dispose()
        objcmd.Dispose()
        objConn.Dispose()

Output :


Note : Give Us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.



No comments:

Post a Comment