Read SQL Table into C# Datatable

Read SQL Table into C# DataTable

Here, give this a shot (this is just a pseudocode)

using System;
using System.Data;
using System.Data.SqlClient;


public class PullDataTest
{
    // your data table
    private DataTable dataTable = new DataTable();

    public PullDataTest()
    {
    }

    // your method to pull data from database to datatable   
    public void PullData()
    {
        string connString = @"your connection string here";
        string query = "select * from table";

        SqlConnection conn = new SqlConnection(connString);        
        SqlCommand cmd = new SqlCommand(query, conn);
        conn.Open();

        // create data adapter
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        // this will query your database and return the result to your datatable
        da.Fill(dataTable);
        conn.Close();
        da.Dispose();
    }
}

How to use DataTable from SQL Server in C#

first question:

you can use gridview for your query if you want to show more than 1 column from your query.

Second question:

and you can use DataSet to get more than 1 table in case you need.

check this:

string queryCustomer = 
"SELECT CustomerID, CompanyName FROM dbo.Customers";
SqlDataAdapter adapter = new SqlDataAdapter(queryCustomer , connection);

DataSet dataSet = new DataSet();
adapter.Fill(dataSet , "Customers");

string queryOrders = 
"SELECT OrderId, Date FROM dbo.Orders";
SqlDataAdapter adapterOrder = new SqlDataAdapter(queryOrders , connection);
adapterOrder.Fill(dataSet , "Orders");

//assume you have a GridView named myGridview.
//than set its DataSource to one of your DataSet tables.

myGridview.DataSource = dataSet.Tables[0] //customers will be the source

Simple import of an MS-SQL table into System.Data.DataTable, is this possible?

Maybe what you need is this.

using (var da = new System.Data.SqlClient.SqlDataAdapter("SELECT ProductID, SupplierID FROM dbo.Products", connection)) 
{
 da.Fill(dt_Products);
}

Quickest way to create DataTable from query?

Use SqlDataAdapter to fill a DataTable.

DataTable dt = new DataTable();
using (SqlConnection yourConnection = new SqlConnection("connectionstring"))
{
    using (SqlCommand cmd = new SqlCommand("....your sql statement", yourConnection))
    {
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            da.Fill(dt);
        }
    }
}

Use using block with your SqlConnection, SqlCommand and SqlDataAdapter since they implement IDisposable interface. Also use Parameterized query

How to insert a data table into SQL Server database table?

Create a User-Defined TableType in your database:

CREATE TYPE [dbo].[MyTableType] AS TABLE(
    [Id] int NOT NULL,
    [Name] [nvarchar](128) NULL
)

and define a parameter in your Stored Procedure:

CREATE PROCEDURE [dbo].[InsertTable]
    @myTableType MyTableType readonly
AS
BEGIN
    insert into [dbo].Records select * from @myTableType 
END

and send your DataTable directly to sql server:

using (var command = new SqlCommand("InsertTable") {CommandType = CommandType.StoredProcedure})
{
    var dt = new DataTable(); //create your own data table
    command.Parameters.Add(new SqlParameter("@myTableType", dt));
    SqlHelper.Exec(command);
}

To edit the values inside stored-procedure, you can declare a local variable with the same type and insert input table into it:

DECLARE @modifiableTableType MyTableType 
INSERT INTO @modifiableTableType SELECT * FROM @myTableType

Then, you can edit @modifiableTableType:

UPDATE @modifiableTableType SET [Name] = 'new value'

Data transfer between DataTable and Sql server

You need to re-initialize the sqlCommand.Parameters inside the for loop. The current problem is that the array of parameters for that particular command (sqlCommand.Parameters) gets a duplicated parameter name on the 2nd iteration because it still has the previous ones.

The solution you linked should be enough. Just clear the parameters immediately before setting them up:

for (int j = 0; j < DataTableView.Columns.Count - 1; j++)
{

    try
    {
        sqlCommand.CommandText = @"INSERT INTO TestTable  VALUES (@Value1, @Value2, @Value3)";
        sqlCommand.Connection = sqlConnection;

        sqlCommand.Parameters.Clear();
        sqlCommand.Parameters.AddWithValue("@Value1",  DataTableView.Rows[i][j]);
        sqlCommand.Parameters.AddWithValue("@Value2",  DataTableView.Rows[i][j]);
        sqlCommand.Parameters.AddWithValue("@Value3",  DataTableView.Rows[i][j]);

        sqlConnection.Open();
        sqlCommand.ExecuteNonQuery();
        sqlConnection.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error");
    }
}

Also make sure to add the TestTable column names and proper order on the INSERT statement:

sqlCommand.CommandText = @"INSERT INTO TestTable (Column1, Column2, Column3)  VALUES (@Value1, @Value2, @Value3)";

How to fill DataTable with SQL Table

You need to modify the method GetData() and add your "experimental" code there, and return t1.

---

Related Topics