473,320 Members | 1,766 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Dynamic Parameters Sql

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+",";

}
}
Jun 27 '08 #1
2 2983
Have a look at the SqlCommandBuilder class, which seems well suited to what
you are trying to do:
http://msdn.microsoft.com/en-us/libr...ndbuilder.aspx

--Pete
"DaveL" <dv*****@sbcglobal.netwrote in message
news:jy****************@newssvr21.news.prodigy.net ...
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+",";

}
}
Jun 27 '08 #2
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 [C# MVP]" <pb*******@nospammaam.yahoo.comwrote in message
news:F9**********************************@microsof t.com...
Have a look at the SqlCommandBuilder class, which seems well suited to
what you are trying to do:
http://msdn.microsoft.com/en-us/libr...ndbuilder.aspx

--Pete
"DaveL" <dv*****@sbcglobal.netwrote in message
news:jy****************@newssvr21.news.prodigy.net ...
>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.Ad d(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+",";

}
}

Jun 27 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
1
by: mtech1 | last post by:
Access 2002 I am trying to create a dynamic crosstab report that parameters come from 3 different forms. I get runtime error 3070 - The Microsoft Jet database engine does not recognize...
5
by: swarsa | last post by:
Hi All, I realize this is not a Palm OS development forum, however, even though my question is about a Palm C program I'm writing, I believe the topics are relevant here. This is because I...
3
by: Stephen Gennard | last post by:
Hello, I having a problem dynamically invoking a static method that takes a reference to a SByte*. If I do it directly it works just fine. Anyone any ideas why? I have include a example...
1
by: sleigh | last post by:
Hello, I'm building a web application that will build a dynamic form based upon questions in a database. This form will have several different sections that consist of a panel containing one to...
7
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always...
3
by: topmind | last post by:
I am generally new to dot.net, coming from "scriptish" web languages such as ColdFusion and Php. I have a few questions if you don't mind. First, how does one go about inserting dynamic SQL...
10
by: jflash | last post by:
Hello all, I feel dumb having to ask this question in the first place, but I just can not figure it out. I am wanting to set my site up using dynamic urls (I'm assuming that's what they're...
4
by: hobbes992 | last post by:
Howdy folks, I've been working on a c project, compiling using gcc, and I've reached a problem. The assignment requires creation of a two-level directory file system. No files have to be added or...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.