I was using SqlCommandBuilder..but i was unable to get my Identity Keys to
reflect in my existing Datatable
so i made a test insert
insert into mytable (Column1) values(@Column1)
;Select Scope_identity() as Column0
I was able to Get SqlDataAdapter to Reflect 100 plus Identity Columns to
Reflect Back in my datatable
when I used SqlcommandBuilder to Do the above
Could not get the Insert and reflect identity keys back into the datatable
I also Appended ":select Scope_Identity() as Column0" to the InsertCommand
of SqlCommandBuilder
Now Im trying to create my own CommandBuilder
the Below retrievs Table Schema from the server and i also created a
CreateInsert function...
excluding SqlCommandBuilder, I was able to insert all the rows using
SqlDataAdapter and also reflect
the newly created Identity keys back into the Existing Datatable
I sure would like to find out why i was un-able to get SqlCommandBuilder to
Work Properly for this
my Table is not a joind table , Just Sql Table -> Datatable
//sample of using sqlcommandbuilder that did not work for my need correctly
//
//*********************************
SqlDataAdapter Adapter = new SqlDataAdapter();
Adapter.SelectCommand= new SqlCommand("Select * from myTable with (nolock)";
SqlCommandBuilder cmd = new SqlCommandBuilder(Adapter);
Adapter.InsertCommand=Cmd.GetInsertCommand();
ds=new DataSet();
oConn.Open();
Adapter.File(ds,"MyTable");
oCon.Close();
// have 0 rows at this point
//parse text file of 100 plus rows
//
Adapter.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
oConn.Open()
Adapter.Update(ds,"MyTable")
ds.AcceptChanges()
**************** above does not work with CommandBuilder, Replace the
insertCommand with myown virtually the same as Sqlcommandbulders
********** i works right all identity ids are reflected in the Datatable
upon completion
Thanks
Dave
//returns datatable with column Properties of a SqlTable
//part of my connection class , so it uses its own connection
public DataTable GetTableSchema(string DatabaseName,String TableName)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.Conn;
DataTable dt = new DataTable();
cmd.CommandText = "select c.Name,"+
"t.Name as Type,"+
"c.max_length as [Len],"+
"c.Precision,"+
"c.is_nullable,"+
"c.is_ansi_padded,"+
"c.is_rowguidcol,"+
"c.is_identity " +
"from sys.tables s "+
"INNER JOIN sys.columns c ON s.object_id =
c.object_id "+
"inner join sys.types t on
t.user_type_id=c.user_type_id "+
"where s.name = '"+TableName+"'";
if (this.OpenDatabase(DatabaseName) == true) //error handling
delt with in the open method
{
SqlDataReader Reader = cmd.ExecuteReader();
dvSqlReader.ReadToTable(Reader, dt);
Reader.Close();
this.Close();
}
return dt;
}
//builds a Insert String Based on Datatable Name and above Table Schema and
any Excluded Columns
public static SqlCommand CreateInsertCommand(DataTable dt, DataTable
Schema,string ExcludeColumns)
{
SqlCommand Cmd = new SqlCommand();
string sInsert = "";
string sParam = "";
int iLen = 0;
int iParamPos = 0;
string sParmName = "";
SqlParameter oParm=null ;
if (ExcludeColumns==null)
{
ExcludeColumns="";
}
sInsert = "Insert Into " + dt.TableName + " (";
sParam = " Values(";
for (int x = 0; x < dt.Columns.Count; x++)
{
if (ExcludeColumns.IndexOf(dt.Columns[x].ColumnName)>-1)
continue;
oParm = new SqlParameter();
sInsert += dt.Columns[x].ColumnName + ",";
//build one name add to parameters
sParmName = "@" + dt.Columns[x].ColumnName;
iLen = Convert.ToInt32(Schema.Rows[x]["Len"]);
if (Schema.Rows[x]["Type"].ToString() == "varchar")
{
oParm.ParameterName = sParmName;
oParm.SqlDbType = SqlDbType.VarChar;
oParm.Size = iLen;
oParm.SourceColumn = dt.Columns[x].ColumnName;
Cmd.Parameters.Add(oParm);
/*
Cmd.Parameters.Add(sParmName,
SqlDbType.VarChar,
iLen,
dt.Columns[x].ColumnName);
*/
}
else if (Schema.Rows[x]["Type"].ToString() == "int")
{
oParm.ParameterName = sParmName;
oParm.SqlDbType = SqlDbType.Int;
oParm.Size = iLen;
oParm.SourceColumn = dt.Columns[x].ColumnName;
Cmd.Parameters.Add(oParm);
/*
Cmd.Parameters.Add(sParmName,
SqlDbType.Int,
iLen,
dt.Columns[x].ColumnName);
*/
}
else if (Schema.Rows[x]["Type"].ToString() ==
"datetime")
{
oParm.ParameterName = sParmName;
oParm.SqlDbType = SqlDbType.DateTime;
oParm.Size = iLen;
oParm.SourceColumn = dt.Columns[x].ColumnName;
Cmd.Parameters.Add(oParm);
/*
Cmd.Parameters.Add(sParmName,
SqlDbType.DateTime,
iLen,
dt.Columns[x].ColumnName);
*/
}
else if (Schema.Rows[x]["Type"].ToString() == "bit")
{
oParm.ParameterName = sParmName;
oParm.SqlDbType = SqlDbType.Bit;
oParm.Size = iLen;
oParm.SourceColumn = dt.Columns[x].ColumnName;
Cmd.Parameters.Add(oParm);
/*
Cmd.Parameters.Add(sParmName,
SqlDbType.Bit,
iLen,
dt.Columns[x].ColumnName);
*/
}
sParam += sParmName + ",";
Console.WriteLine(Cmd.Parameters[iParamPos].ParameterName);
iParamPos++;
// Console.ReadKey();
}
sInsert = sInsert.Substring(0, sInsert.Length - 1)+")";
sInsert += " " + sParam.Substring(0, sParam.Length - 1)+")";
Cmd.CommandText=sInsert;
Console.WriteLine(Cmd.CommandText);
Console.ReadKey();
return Cmd;
}
Peter Bromberg said:
Have a look at the SqlCommandBuilder class, which seems well suited to
what you are trying to do:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx
--Pete
DaveL said:
Hello
I want to build Dynamic Paramers for a Sql Insert
below is what i have so far but...determinthe column type and length im
having Problems with
Tks
dave
string sInsert = "Insert into oaDental..dentalClaims (";
string sParam = "Values(";
string sParmName = "";
for (int x = 0; x < this.ds.Tables[0].Columns.Count; x++)
{
if (this.ds.Tables[0].Columns[x].ColumnName !=
"MyIdentity")
{
sInsert += "'" +
this.ds.Tables[0].Columns[x].ColumnName + "',";
//build one name add to parameters
sParmName = "@" +
this.ds.Tables[0].Columns[x].ColumnName;
/*
this.SqlConn1.Adapter.InsertCommand.Parameters.Add(sParmName,
System.Type.GetType(this.ds.Tables[0].Columns[x].DataType.ToString()),
//crashes here ino this is wrong , what i correct
this.ds.Tables[0].Columns[x].MaxLength,
this.ds.Tables[0].Columns[x].ColumnName);
*/
sParam += sParmName+",";
}
}
