Most straightforward way to add a row to an SQL Server table in ADO.NET without hardcoded SQL?

Question

Hi,

I am wondering what the best / most efficient / common way is to add a row to an SQL Server table using C# and ADO.NET. I know of course that I can just create an SQL statement for that, but first, the destination table schema might vary, so I want to keep this flexible, and second, there are so much columns that I do not want to code and maintain this manually. So I currently use a SqlCommandBuilder that is automatically creating the proper insert statement for me, together with an SQLDataAdapter, like this:

var dataAdapter = new SqlDataAdapter("select * from sometable", _databaseConnection);
new SqlCommandBuilder(dataAdapter);
dataAdapter.Fill(dataTable);

// ... add row to dataTable, fill fields from some external file that 
// ... includes column names as well, 
//.... add some more field values not from the file, etc. ...

dataAdapter.Update(dataTable);

This seems pretty inefficient though to first grab all the records from the table even though I do not need them for anything (especially considering that there might even already be a million records in there). Using some select statement like select * from sometable where 1=2 would work, but it does not seem like a very clean approach. I imagine there is some different solution for this that I am just not aware of.

Thanks,
Timo

Answer 1

I think the best way to insert rows is by using Stored Procedures through the ADO.NET command object.

If you are inserting massive amounts of data and are using SQL Server 2008 you can pass DataTable objects to a stored procedure by using a User-Defined Table Types.

In SQL:

CREATE TYPE SAMPLE_TABLE_TYPE --
AS
field1 VARCHAR(255)
field2 VARCHAR(255)

CREATE STORED PROCEDURE insert_data
AS
@data Sample_TABLE_TYPE
BEGIN
INSERT INTO table1 (field1, field1) 
SELECT username, password FROM @data;

In .NET:

DataTable myTable = new DataTable();
myTable.Columns.Add(new DataColumn("field1", typeof(string));
myTable.Columns.Add(new DataColumn("field1", typeof(string));
SqlCommand command = new SqlCommand(conn, CommandType.StoredProcedure);
command.Parameters.Add("@data", myTable);
command.ExecuteNonQuery();

If you data also contains updates you can use the new MERGE function used in SQL Server 2008 to efficiently perform both inserts and updates in the same procedure.

However, if creating User-Defined Table Types and creating stored procedures is too much work, and you need a complete dynamic solution I would stick with what you have, with the recommendation of using the

Where 1 = 0

appended to your SQL text.