Select all columns except the first column for any given SQL Server table

c# sqlbulkcopy sql-server

Question

I have the following C# code, but I need it to pick ALL columns WITHOUT the first column of the table (the identity column), so that the destination database would set its own identity column values when I insert the data into a similar table there:

SqlCommand commandSourceData = new SqlCommand($"SELECT * FROM dbo.{tableName};", sourceConnection);
SqlDataReader reader = commandSourceData.ExecuteReader();

Exists a method to do this?

1
1
12/21/2017 5:44:56 PM

Accepted Answer

Use this sort of code if you want a universal answer for each column in your database.

public string GetColumnsWithoutIdentity(string tableName, SqlConnection con)
{
    SqlDataAdapter da = new SqlDataAdapter($"SELECT * FROM dbo.{tableName} where 1=0", con);
    DataTable dt = new DataTable();
    da.FillSchema(dt, SchemaType.Source);
    var cols = dt.Columns.Cast<DataColumn>().Where(x => !x.AutoIncrement).Select(x => x.ColumnName);
    return string.Join(",", cols);
}

You may now create a SQL statement without the autoincrement column using the returned string.
Note the SQL Injection vulnerability in this code. Make absolutely certain that your user did not explicitly write the tableName parameter used to generate the initial query. Set up a whitelist (read-only) of preset tables for it to choose from (and also this is not 100 percent safe)

The need to hit the database twice is another issue. A datatable must first be filled up before the schema containing information about the AutoIncrement column can be obtained.

4
12/18/2017 7:41:22 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow