Creating a SQL Server Table from a C# Datatable

Creating a SQL Server table from a C# datatable

It's a little bit unusual in SQL to create tables out of a client supplied definition of a Datatable object. Tables are carefully crafted entities in SQL, with deploy time placement consideration of choosing the proper disk, with indexing consideration at design time and with all the issues involved in properly modeling a database.

Better you'd explain what you're trying to achieve so we understand what advice to give.

As a side note, in SQL 2008 there is a very easy way to create a table out of a client defined Datatable: pass the DataTable as a Table value parameter, then issue a SELECT * INTO <tablename> FROM @tvp, this will effectively transfer the definition of the Datatable and its content data into a real table in SQL.

Create SQL Table based on Datatable C#

This worked for me in linqpad: ( after adding a nuget reference to "Microsoft.SQLServer.SMO"

copied and modified from answer at: Script table as CREATE TO by using vb.net

I had trouble trying to access Tables["[exd].[ABCINDICATORSET]"], couldn't figure out how to specify a table and domain properly, I was always getting null back.

// Define your database and table you want to script out
string dbName = "Ivara77Install";

// set up the SMO server objects - I'm using "integrated security" here for simplicity
Server srv = new Server();
srv.ConnectionContext.LoginSecure = true;
srv.ConnectionContext.ServerInstance = ".";

// get the database in question
Database db = new Database();
db = srv.Databases[dbName];

StringBuilder sb = new StringBuilder();

// define the scripting options - what options to include or not
ScriptingOptions options = new ScriptingOptions();
options.ClusteredIndexes = true;
options.Default = true;
options.DriAll = true;
options.Indexes = true;
options.IncludeHeaders = true;

// script out the table's creation 
Table tbl = db.Tables.OfType<Table>().Single(t => t.Schema.ToLower() == "exd" && t.Name.ToLower() == "ABCINDICATORSET".ToLower() );

StringCollection coll = tbl.Script(options);

foreach (string str in coll)
{
    sb.Append(str);
    sb.Append(Environment.NewLine);
}

// you can get the string that makes up the CREATE script here
// do with this CREATE script whatever you like!
string createScript = sb.ToString();

Some of the sql is slightly more verbose than what you get from sql server when you do Script Table As -> Create To -> New Query Editor Window

The changes to make it closer to what sql server generates were:

//options.Indexes = true;
options.IncludeHeaders = true;
options.NoCollation = true;

C# insert data from datatable to SQL Server database

"INSERT INTO tblTemp (FNCL_SPLIT_REC_ID, PROJ_ID, SALES_SRC_PRC) VALUES ("
                    + dt.Rows[i]["FNCL_SPLIT_REC_ID"].ToString().Trim() + ",'"
                    + dt.Rows[i]["PROJ_ID"].ToString().Trim() + "',"
                    + dt.Rows[i]["SALES_SRC_PRC"].ToString().Trim() + ");";

Removed the ' ' between FNCL_SPLIT_REC_ID as it is int and SALES_SRC_PRC since it is money.

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'

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

C# DataTable into SQL Server stored procedure

Create a user defined table type in SQL Server:

CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50));
GO

And pass this table valued parameter to the stored procedure :

CREATE PROCEDURE [Sade].[SPLocationInsert]
 @... INT
,@... VARCHAR(64)
,@... VARCHAR(MAX)
,@... INT
,@... INT
,@... INT
,@... INT
,@... INT
,@PLocationDataList LocationTableType READONLY
AS 
    SET NOCOUNT ON
    -- insert your code here
GO

References:

User defined table types : https://technet.microsoft.com/en-us/library/bb522526(v=sql.105).aspx

Table valued parameters: https://technet.microsoft.com/en-us/library/bb510489(v=sql.105).aspx

---

Related Topics