I like the drag-and-drop accessibility of dragging a table to a Web Forms
designer and seeing a SqlDataAdapter automatically created for me.. being
able to create a DataSet from that is fun and exciting, because now with
this schema based data outline Visual Studio provides me with a typed class
for managing data complete with Intellisense menus exposing my own field
names as C# object properties ... cool ..
Anyway, I have a problem. I am building this site for a client who hosts
their own database, so my database is a test database. I have a different
database connection string in my web.config file than my client's connection
string in their web.config file. That file is the only file I want to differ
between my development environment and the deployment environment.
So now how do I get this cool functionality? I don't want to test and debug
using live data. I'm stuck now because the connection string in the created
connection object when dragging and dropping data adapters is hard-coded,
and worse it is hard-coded into the specific web forms I'm dragging to.
So I suppose I could change the connection string at runtime, but I would
really rather not have to do this for every web form that uses it. Is there
a way I can clean this up and consolidate my connection string while using
this functionality?
Jon 9 994
Hi Jon,
Our solution is to detect the servername at runtime using code:
Dim strSERVER_NAME as string
strSERVER_NAME = Replace(Request.ServerVariables("SERVER_NAME"), ".", "_")
strDataBaseServer = ConfigurationSettings.AppSettings(strSERVER_NAME &
"_DataSource")
and store the appropriate value for its database in the web.config file.
<add key="localhost_DataSource" value="sqlserverdev" />
<add key="www_mysite_com_DataSource" value="sqlserverproduction" />
The application automatically finds its data wherever it is run.
Does this help?
Ken
Microsoft MVP [ASP.NET]
"Jon Davis" <jo*@REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl... I like the drag-and-drop accessibility of dragging a table to a Web Forms designer and seeing a SqlDataAdapter automatically created for me.. being able to create a DataSet from that is fun and exciting, because now with this schema based data outline Visual Studio provides me with a typed class for managing data complete with Intellisense menus exposing my own field names as C# object properties ... cool ..
Anyway, I have a problem. I am building this site for a client who hosts their own database, so my database is a test database. I have a different database connection string in my web.config file than my client's connection string in their web.config file. That file is the only file I want to differ between my development environment and the deployment environment.
So now how do I get this cool functionality? I don't want to test and debug using live data. I'm stuck now because the connection string in the created connection object when dragging and dropping data adapters is hard-coded, and worse it is hard-coded into the specific web forms I'm dragging to.
So I suppose I could change the connection string at runtime, but I would really rather not have to do this for every web form that uses it. Is there a way I can clean this up and consolidate my connection string while using this functionality?
Jon
Thank you, Ken, for effectively wasting my time under the guise of help.
Actually, I do thank you (sincerely) for trying to help, but it would be
more helpful if you actually read what I asked.
I already said that I store the connection string in the web.config file and
could refer to it at runtime.
My question is regarding the use of the IDE's designer (in VS.Net) for the
OleDbAdapter. How do I cause it to refer to the web.config connection string
rather than have it hard-code a connection object, in an inaccessible
fashion being as it is in the "DO NOT MODIFY" section, for every INSERT,
SELECT, UPDATE, and DELETE it may want to do?
I've decided to throw out the use of the IDE's Data toolbox drag-and-drop
abilities, unless someone can give me a solution. What a tragic loss of
productivity though.
Jon
"Ken Cox [Microsoft MVP]" <BA************@sympatico.ca> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl... Hi Jon,
Our solution is to detect the servername at runtime using code:
Dim strSERVER_NAME as string strSERVER_NAME = Replace(Request.ServerVariables("SERVER_NAME"), ".",
"_") strDataBaseServer = ConfigurationSettings.AppSettings(strSERVER_NAME & "_DataSource")
and store the appropriate value for its database in the web.config file.
<add key="localhost_DataSource" value="sqlserverdev" /> <add key="www_mysite_com_DataSource" value="sqlserverproduction" />
The application automatically finds its data wherever it is run.
Does this help?
Ken Microsoft MVP [ASP.NET] "Jon Davis" <jo*@REMOVE.ME.PLEASE.jondavis.net> wrote in message news:%2****************@TK2MSFTNGP09.phx.gbl...I like the drag-and-drop accessibility of dragging a table to a Web Forms designer and seeing a SqlDataAdapter automatically created for me..
being able to create a DataSet from that is fun and exciting, because now with this schema based data outline Visual Studio provides me with a typed class for managing data complete with Intellisense menus exposing my own field names as C# object properties ... cool ..
Anyway, I have a problem. I am building this site for a client who hosts their own database, so my database is a test database. I have a
different database connection string in my web.config file than my client's connection string in their web.config file. That file is the only file I want to differ between my development environment and the deployment environment.
So now how do I get this cool functionality? I don't want to test and debug using live data. I'm stuck now because the connection string in the created connection object when dragging and dropping data adapters is
hard-coded, and worse it is hard-coded into the specific web forms I'm dragging to.
So I suppose I could change the connection string at runtime, but I
would really rather not have to do this for every web form that uses it. Is there a way I can clean this up and consolidate my connection string while
using this functionality?
Jon
I've discussed this with the VS.NET developers. I think it would be an
important feature to add for VS.NET 2005. I'll bring it up again.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Jon Davis" <jo*@REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:u6*************@TK2MSFTNGP10.phx.gbl... Thank you, Ken, for effectively wasting my time under the guise of help. Actually, I do thank you (sincerely) for trying to help, but it would be more helpful if you actually read what I asked.
I already said that I store the connection string in the web.config file
and could refer to it at runtime.
My question is regarding the use of the IDE's designer (in VS.Net) for the OleDbAdapter. How do I cause it to refer to the web.config connection
string rather than have it hard-code a connection object, in an inaccessible fashion being as it is in the "DO NOT MODIFY" section, for every INSERT, SELECT, UPDATE, and DELETE it may want to do?
I've decided to throw out the use of the IDE's Data toolbox drag-and-drop abilities, unless someone can give me a solution. What a tragic loss of productivity though.
Jon
"Ken Cox [Microsoft MVP]" <BA************@sympatico.ca> wrote in message news:%2****************@TK2MSFTNGP11.phx.gbl... Hi Jon,
Our solution is to detect the servername at runtime using code:
Dim strSERVER_NAME as string strSERVER_NAME = Replace(Request.ServerVariables("SERVER_NAME"), ".", "_") strDataBaseServer = ConfigurationSettings.AppSettings(strSERVER_NAME & "_DataSource")
and store the appropriate value for its database in the web.config
file. <add key="localhost_DataSource" value="sqlserverdev" /> <add key="www_mysite_com_DataSource" value="sqlserverproduction" />
The application automatically finds its data wherever it is run.
Does this help?
Ken Microsoft MVP [ASP.NET] "Jon Davis" <jo*@REMOVE.ME.PLEASE.jondavis.net> wrote in message news:%2****************@TK2MSFTNGP09.phx.gbl...I like the drag-and-drop accessibility of dragging a table to a Web
Forms designer and seeing a SqlDataAdapter automatically created for me..
being able to create a DataSet from that is fun and exciting, because now
with this schema based data outline Visual Studio provides me with a typed class for managing data complete with Intellisense menus exposing my own
field names as C# object properties ... cool ..
Anyway, I have a problem. I am building this site for a client who
hosts their own database, so my database is a test database. I have a different database connection string in my web.config file than my client's connection string in their web.config file. That file is the only file I want to differ between my development environment and the deployment environment.
So now how do I get this cool functionality? I don't want to test and debug using live data. I'm stuck now because the connection string in the created connection object when dragging and dropping data adapters is hard-coded, and worse it is hard-coded into the specific web forms I'm dragging
to. So I suppose I could change the connection string at runtime, but I would really rather not have to do this for every web form that uses it. Is there a way I can clean this up and consolidate my connection string while using this functionality?
Jon
*sigh* Basically what I want is the ability to generate an OleDbDataAdapter on the fly, complete with its Update and Delete and Insert and Select commands readily available for automatic updates with DataSets, but while having full web.config control of the connection object.
Well, I've written up a temporary workaround. Note that this is NOT thoroughly tested, and is probably very buggy. It's a prototype, and it works good enough for me for now ... I think ...
[code follows]
public static OleDbDataAdapter GenerateTableDataAdapter(string tableName, string[] keyFields, string whereConditional) {
OleDbConnection dbConn = DbConnection;
OleDbDataReader dr = Db.ExecQueryCmd("SELECT * FROM " + tableName);
ArrayList fieldsAR = new ArrayList();
for (int f=0; f<dr.FieldCount; f++) {
fieldsAR.Add(dr.GetName(f));
}
string[] fields = (string[])fieldsAR.ToArray(typeof(string));
ArrayList al = new ArrayList();
foreach (string field in fields) {
al.Add(new System.Data.Common.DataColumnMapping(field, field));
}
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM " + tableName, dbConn);
System.Data.Common.DataColumnMapping[] mappings = (System.Data.Common.DataColumnMapping[])al.ToArray(typeof(System.Data.Common.DataColumnMa pping));
da.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "Users", mappings)});
string sSQL = "SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (whereConditional != null && whereConditional != "")
sSQL += " WHERE " + whereConditional;
OleDbCommand selectCmd = new OleDbCommand(sSQL, dbConn);
da.SelectCommand = selectCmd;
sSQL = "INSERT INTO " + tableName + " (";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += ") VALUES (";
for (int dci=0; dci<fields.Length; dci++) {
sSQL += "?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += "); SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (keyFields != null) {
if (keyFields.Length > 0) {
sSQL += " WHERE ";
foreach (string keyfield in keyFields) {
sSQL += "(" + keyfield + " = ?) AND ";
}
}
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
OleDbCommand insertCmd = new OleDbCommand(sSQL, dbConn);
//foreach (string field in fields) {
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter();
p.ParameterName = field;
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
insertCmd.Parameters.Add(p);
}
if (keyFields != null) {
if (keyFields.Length > 0) {
foreach (string keyfield in keyFields) {
OleDbParameter p = new OleDbParameter();
p.ParameterName = "Select_" + keyfield;
string dt = dr.GetDataTypeName(dr.GetOrdinal(keyfield));
switch (dt) {
case "String":
case "System.String":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = keyfield;
insertCmd.Parameters.Add(p);
}
}
}
da.InsertCommand = insertCmd;
sSQL = "UPDATE " + tableName + " SET ";
foreach (string field in fields) {
sSQL += field + " = ?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " WHERE ";
foreach (string field in fields) {
sSQL += field + " = ?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += "; SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (keyFields != null) {
if (keyFields.Length > 0) {
sSQL += " WHERE ";
foreach (string keyfield in keyFields) {
sSQL += "(" + keyfield + " = ?) AND ";
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
}
}
OleDbCommand updateCmd = new OleDbCommand(sSQL, dbConn);
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter();
p.ParameterName = field;
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
updateCmd.Parameters.Add(p);
}
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter("Original_" + field, OleDbType.VarChar, 20, ParameterDirection.Input, true, (byte)0, (byte)0, field, System.Data.DataRowVersion.Original, null);
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
updateCmd.Parameters.Add(p);
}
if (keyFields != null) {
if (keyFields.Length > 0) {
foreach (string keyfield in keyFields) {
OleDbParameter p = new OleDbParameter();
p.ParameterName = "Select_" + keyfield;
string dt = dr.GetDataTypeName(dr.GetOrdinal(keyfield));
switch (dt) {
case "String":
case "System.String":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = keyfield;
updateCmd.Parameters.Add(p);
}
}
}
da.UpdateCommand = updateCmd;
sSQL = "DELETE FROM " + tableName;
if (dr.FieldCount > 0) sSQL += " WHERE ";
foreach (string field in fields) {
sSQL += "(" + field + " = ?) AND ";
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
OleDbCommand deleteCmd = new OleDbCommand(sSQL, dbConn);
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter("Original_" + field, OleDbType.VarChar, 20, ParameterDirection.Input, true, (byte)0, (byte)0, field, System.Data.DataRowVersion.Original, null);
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
deleteCmd.Parameters.Add(p);
}
da.DeleteCommand = deleteCmd;
return da;
}
public static OleDbDataAdapter GenerateTableDataAdapter(string tableName, params string[] keyFields) {
return GenerateTableDataAdapter(tableName, keyFields, "");
}
*sigh* Basically what I want is the ability to generate an OleDbDataAdapter on the fly, complete with its Update and Delete and Insert and Select commands readily available for automatic updates with DataSets, but while having full web.config control of the connection object.
Well, I've written up a temporary workaround. Note that this is NOT thoroughly tested, and is probably very buggy. It's a prototype, and it works good enough for me for now ... I think ...
[code follows]
public static OleDbDataAdapter GenerateTableDataAdapter(string tableName, string[] keyFields, string whereConditional) {
OleDbConnection dbConn = DbConnection;
OleDbDataReader dr = Db.ExecQueryCmd("SELECT * FROM " + tableName);
ArrayList fieldsAR = new ArrayList();
for (int f=0; f<dr.FieldCount; f++) {
fieldsAR.Add(dr.GetName(f));
}
string[] fields = (string[])fieldsAR.ToArray(typeof(string));
ArrayList al = new ArrayList();
foreach (string field in fields) {
al.Add(new System.Data.Common.DataColumnMapping(field, field));
}
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM " + tableName, dbConn);
System.Data.Common.DataColumnMapping[] mappings = (System.Data.Common.DataColumnMapping[])al.ToArray(typeof(System.Data.Common.DataColumnMa pping));
da.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "Users", mappings)});
string sSQL = "SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (whereConditional != null && whereConditional != "")
sSQL += " WHERE " + whereConditional;
OleDbCommand selectCmd = new OleDbCommand(sSQL, dbConn);
da.SelectCommand = selectCmd;
sSQL = "INSERT INTO " + tableName + " (";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += ") VALUES (";
for (int dci=0; dci<fields.Length; dci++) {
sSQL += "?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += "); SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (keyFields != null) {
if (keyFields.Length > 0) {
sSQL += " WHERE ";
foreach (string keyfield in keyFields) {
sSQL += "(" + keyfield + " = ?) AND ";
}
}
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
OleDbCommand insertCmd = new OleDbCommand(sSQL, dbConn);
//foreach (string field in fields) {
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter();
p.ParameterName = field;
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
insertCmd.Parameters.Add(p);
}
if (keyFields != null) {
if (keyFields.Length > 0) {
foreach (string keyfield in keyFields) {
OleDbParameter p = new OleDbParameter();
p.ParameterName = "Select_" + keyfield;
string dt = dr.GetDataTypeName(dr.GetOrdinal(keyfield));
switch (dt) {
case "String":
case "System.String":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = keyfield;
insertCmd.Parameters.Add(p);
}
}
}
da.InsertCommand = insertCmd;
sSQL = "UPDATE " + tableName + " SET ";
foreach (string field in fields) {
sSQL += field + " = ?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " WHERE ";
foreach (string field in fields) {
sSQL += field + " = ?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += "; SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (keyFields != null) {
if (keyFields.Length > 0) {
sSQL += " WHERE ";
foreach (string keyfield in keyFields) {
sSQL += "(" + keyfield + " = ?) AND ";
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
}
}
OleDbCommand updateCmd = new OleDbCommand(sSQL, dbConn);
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter();
p.ParameterName = field;
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
updateCmd.Parameters.Add(p);
}
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter("Original_" + field, OleDbType.VarChar, 20, ParameterDirection.Input, true, (byte)0, (byte)0, field, System.Data.DataRowVersion.Original, null);
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
updateCmd.Parameters.Add(p);
}
if (keyFields != null) {
if (keyFields.Length > 0) {
foreach (string keyfield in keyFields) {
OleDbParameter p = new OleDbParameter();
p.ParameterName = "Select_" + keyfield;
string dt = dr.GetDataTypeName(dr.GetOrdinal(keyfield));
switch (dt) {
case "String":
case "System.String":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = keyfield;
updateCmd.Parameters.Add(p);
}
}
}
da.UpdateCommand = updateCmd;
sSQL = "DELETE FROM " + tableName;
if (dr.FieldCount > 0) sSQL += " WHERE ";
foreach (string field in fields) {
sSQL += "(" + field + " = ?) AND ";
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
OleDbCommand deleteCmd = new OleDbCommand(sSQL, dbConn);
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter("Original_" + field, OleDbType.VarChar, 20, ParameterDirection.Input, true, (byte)0, (byte)0, field, System.Data.DataRowVersion.Original, null);
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
deleteCmd.Parameters.Add(p);
}
da.DeleteCommand = deleteCmd;
return da;
}
public static OleDbDataAdapter GenerateTableDataAdapter(string tableName, params string[] keyFields) {
return GenerateTableDataAdapter(tableName, keyFields, "");
}
Hm no doesn't work .. no way to debug this thing :`(
Tells me "invalid syntax near ',' " but there's no way to see the ACTUAL sql used in the update query with the ?'s. I'm confused .. and tired ..
try {
da.Update(dsUsers);
} catch (Exception ex) {
Response.Write(ex.Message + "\r\n\r\nSQL:\r\n" + da.UpdateCommand.CommandText);
Response.End();
}
"Jon Davis" <jo*@REMOVE.ME.PLEASE.jondavis.net> wrote in message news:uF**************@TK2MSFTNGP10.phx.gbl...
*sigh* Basically what I want is the ability to generate an OleDbDataAdapter on the fly, complete with its Update and Delete and Insert and Select commands readily available for automatic updates with DataSets, but while having full web.config control of the connection object.
Well, I've written up a temporary workaround. Note that this is NOT thoroughly tested, and is probably very buggy. It's a prototype, and it works good enough for me for now ... I think ...
[code follows]
public static OleDbDataAdapter GenerateTableDataAdapter(string tableName, string[] keyFields, string whereConditional) {
OleDbConnection dbConn = DbConnection;
OleDbDataReader dr = Db.ExecQueryCmd("SELECT * FROM " + tableName);
ArrayList fieldsAR = new ArrayList();
for (int f=0; f<dr.FieldCount; f++) {
fieldsAR.Add(dr.GetName(f));
}
string[] fields = (string[])fieldsAR.ToArray(typeof(string));
ArrayList al = new ArrayList();
foreach (string field in fields) {
al.Add(new System.Data.Common.DataColumnMapping(field, field));
}
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM " + tableName, dbConn);
System.Data.Common.DataColumnMapping[] mappings = (System.Data.Common.DataColumnMapping[])al.ToArray(typeof(System.Data.Common.DataColumnMa pping));
da.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "Users", mappings)});
string sSQL = "SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (whereConditional != null && whereConditional != "")
sSQL += " WHERE " + whereConditional;
OleDbCommand selectCmd = new OleDbCommand(sSQL, dbConn);
da.SelectCommand = selectCmd;
sSQL = "INSERT INTO " + tableName + " (";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += ") VALUES (";
for (int dci=0; dci<fields.Length; dci++) {
sSQL += "?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += "); SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (keyFields != null) {
if (keyFields.Length > 0) {
sSQL += " WHERE ";
foreach (string keyfield in keyFields) {
sSQL += "(" + keyfield + " = ?) AND ";
}
}
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
OleDbCommand insertCmd = new OleDbCommand(sSQL, dbConn);
//foreach (string field in fields) {
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter();
p.ParameterName = field;
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
insertCmd.Parameters.Add(p);
}
if (keyFields != null) {
if (keyFields.Length > 0) {
foreach (string keyfield in keyFields) {
OleDbParameter p = new OleDbParameter();
p.ParameterName = "Select_" + keyfield;
string dt = dr.GetDataTypeName(dr.GetOrdinal(keyfield));
switch (dt) {
case "String":
case "System.String":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = keyfield;
insertCmd.Parameters.Add(p);
}
}
}
da.InsertCommand = insertCmd;
sSQL = "UPDATE " + tableName + " SET ";
foreach (string field in fields) {
sSQL += field + " = ?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " WHERE ";
foreach (string field in fields) {
sSQL += field + " = ?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += "; SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (keyFields != null) {
if (keyFields.Length > 0) {
sSQL += " WHERE ";
foreach (string keyfield in keyFields) {
sSQL += "(" + keyfield + " = ?) AND ";
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
}
}
OleDbCommand updateCmd = new OleDbCommand(sSQL, dbConn);
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter();
p.ParameterName = field;
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
updateCmd.Parameters.Add(p);
}
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter("Original_" + field, OleDbType.VarChar, 20, ParameterDirection.Input, true, (byte)0, (byte)0, field, System.Data.DataRowVersion.Original, null);
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
updateCmd.Parameters.Add(p);
}
if (keyFields != null) {
if (keyFields.Length > 0) {
foreach (string keyfield in keyFields) {
OleDbParameter p = new OleDbParameter();
p.ParameterName = "Select_" + keyfield;
string dt = dr.GetDataTypeName(dr.GetOrdinal(keyfield));
switch (dt) {
case "String":
case "System.String":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = keyfield;
updateCmd.Parameters.Add(p);
}
}
}
da.UpdateCommand = updateCmd;
sSQL = "DELETE FROM " + tableName;
if (dr.FieldCount > 0) sSQL += " WHERE ";
foreach (string field in fields) {
sSQL += "(" + field + " = ?) AND ";
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
OleDbCommand deleteCmd = new OleDbCommand(sSQL, dbConn);
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter("Original_" + field, OleDbType.VarChar, 20, ParameterDirection.Input, true, (byte)0, (byte)0, field, System.Data.DataRowVersion.Original, null);
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
deleteCmd.Parameters.Add(p);
}
da.DeleteCommand = deleteCmd;
return da;
}
public static OleDbDataAdapter GenerateTableDataAdapter(string tableName, params string[] keyFields) {
return GenerateTableDataAdapter(tableName, keyFields, "");
}
Hm no doesn't work .. no way to debug this thing :`(
Tells me "invalid syntax near ',' " but there's no way to see the ACTUAL sql used in the update query with the ?'s. I'm confused .. and tired ..
try {
da.Update(dsUsers);
} catch (Exception ex) {
Response.Write(ex.Message + "\r\n\r\nSQL:\r\n" + da.UpdateCommand.CommandText);
Response.End();
}
"Jon Davis" <jo*@REMOVE.ME.PLEASE.jondavis.net> wrote in message news:uF**************@TK2MSFTNGP10.phx.gbl...
*sigh* Basically what I want is the ability to generate an OleDbDataAdapter on the fly, complete with its Update and Delete and Insert and Select commands readily available for automatic updates with DataSets, but while having full web.config control of the connection object.
Well, I've written up a temporary workaround. Note that this is NOT thoroughly tested, and is probably very buggy. It's a prototype, and it works good enough for me for now ... I think ...
[code follows]
public static OleDbDataAdapter GenerateTableDataAdapter(string tableName, string[] keyFields, string whereConditional) {
OleDbConnection dbConn = DbConnection;
OleDbDataReader dr = Db.ExecQueryCmd("SELECT * FROM " + tableName);
ArrayList fieldsAR = new ArrayList();
for (int f=0; f<dr.FieldCount; f++) {
fieldsAR.Add(dr.GetName(f));
}
string[] fields = (string[])fieldsAR.ToArray(typeof(string));
ArrayList al = new ArrayList();
foreach (string field in fields) {
al.Add(new System.Data.Common.DataColumnMapping(field, field));
}
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM " + tableName, dbConn);
System.Data.Common.DataColumnMapping[] mappings = (System.Data.Common.DataColumnMapping[])al.ToArray(typeof(System.Data.Common.DataColumnMa pping));
da.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "Users", mappings)});
string sSQL = "SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (whereConditional != null && whereConditional != "")
sSQL += " WHERE " + whereConditional;
OleDbCommand selectCmd = new OleDbCommand(sSQL, dbConn);
da.SelectCommand = selectCmd;
sSQL = "INSERT INTO " + tableName + " (";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += ") VALUES (";
for (int dci=0; dci<fields.Length; dci++) {
sSQL += "?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += "); SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (keyFields != null) {
if (keyFields.Length > 0) {
sSQL += " WHERE ";
foreach (string keyfield in keyFields) {
sSQL += "(" + keyfield + " = ?) AND ";
}
}
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
OleDbCommand insertCmd = new OleDbCommand(sSQL, dbConn);
//foreach (string field in fields) {
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter();
p.ParameterName = field;
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
insertCmd.Parameters.Add(p);
}
if (keyFields != null) {
if (keyFields.Length > 0) {
foreach (string keyfield in keyFields) {
OleDbParameter p = new OleDbParameter();
p.ParameterName = "Select_" + keyfield;
string dt = dr.GetDataTypeName(dr.GetOrdinal(keyfield));
switch (dt) {
case "String":
case "System.String":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = keyfield;
insertCmd.Parameters.Add(p);
}
}
}
da.InsertCommand = insertCmd;
sSQL = "UPDATE " + tableName + " SET ";
foreach (string field in fields) {
sSQL += field + " = ?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " WHERE ";
foreach (string field in fields) {
sSQL += field + " = ?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += "; SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (keyFields != null) {
if (keyFields.Length > 0) {
sSQL += " WHERE ";
foreach (string keyfield in keyFields) {
sSQL += "(" + keyfield + " = ?) AND ";
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
}
}
OleDbCommand updateCmd = new OleDbCommand(sSQL, dbConn);
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter();
p.ParameterName = field;
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
updateCmd.Parameters.Add(p);
}
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter("Original_" + field, OleDbType.VarChar, 20, ParameterDirection.Input, true, (byte)0, (byte)0, field, System.Data.DataRowVersion.Original, null);
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
updateCmd.Parameters.Add(p);
}
if (keyFields != null) {
if (keyFields.Length > 0) {
foreach (string keyfield in keyFields) {
OleDbParameter p = new OleDbParameter();
p.ParameterName = "Select_" + keyfield;
string dt = dr.GetDataTypeName(dr.GetOrdinal(keyfield));
switch (dt) {
case "String":
case "System.String":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = keyfield;
updateCmd.Parameters.Add(p);
}
}
}
da.UpdateCommand = updateCmd;
sSQL = "DELETE FROM " + tableName;
if (dr.FieldCount > 0) sSQL += " WHERE ";
foreach (string field in fields) {
sSQL += "(" + field + " = ?) AND ";
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
OleDbCommand deleteCmd = new OleDbCommand(sSQL, dbConn);
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter("Original_" + field, OleDbType.VarChar, 20, ParameterDirection.Input, true, (byte)0, (byte)0, field, System.Data.DataRowVersion.Original, null);
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
deleteCmd.Parameters.Add(p);
}
da.DeleteCommand = deleteCmd;
return da;
}
public static OleDbDataAdapter GenerateTableDataAdapter(string tableName, params string[] keyFields) {
return GenerateTableDataAdapter(tableName, keyFields, "");
}
Aha! Found this ... http://msdn.microsoft.com/library/en...asp?frame=true
Jon
"Jon Davis" <jo*@REMOVE.ME.PLEASE.jondavis.net> wrote in message news:Oy**************@TK2MSFTNGP12.phx.gbl...
Hm no doesn't work .. no way to debug this thing :`(
Tells me "invalid syntax near ',' " but there's no way to see the ACTUAL sql used in the update query with the ?'s. I'm confused .. and tired ..
try {
da.Update(dsUsers);
} catch (Exception ex) {
Response.Write(ex.Message + "\r\n\r\nSQL:\r\n" + da.UpdateCommand.CommandText);
Response.End();
}
"Jon Davis" <jo*@REMOVE.ME.PLEASE.jondavis.net> wrote in message news:uF**************@TK2MSFTNGP10.phx.gbl...
*sigh* Basically what I want is the ability to generate an OleDbDataAdapter on the fly, complete with its Update and Delete and Insert and Select commands readily available for automatic updates with DataSets, but while having full web.config control of the connection object.
Well, I've written up a temporary workaround. Note that this is NOT thoroughly tested, and is probably very buggy. It's a prototype, and it works good enough for me for now ... I think ...
[code follows]
public static OleDbDataAdapter GenerateTableDataAdapter(string tableName, string[] keyFields, string whereConditional) {
OleDbConnection dbConn = DbConnection;
OleDbDataReader dr = Db.ExecQueryCmd("SELECT * FROM " + tableName);
ArrayList fieldsAR = new ArrayList();
for (int f=0; f<dr.FieldCount; f++) {
fieldsAR.Add(dr.GetName(f));
}
string[] fields = (string[])fieldsAR.ToArray(typeof(string));
ArrayList al = new ArrayList();
foreach (string field in fields) {
al.Add(new System.Data.Common.DataColumnMapping(field, field));
}
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM " + tableName, dbConn);
System.Data.Common.DataColumnMapping[] mappings = (System.Data.Common.DataColumnMapping[])al.ToArray(typeof(System.Data.Common.DataColumnMa pping));
da.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "Users", mappings)});
string sSQL = "SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (whereConditional != null && whereConditional != "")
sSQL += " WHERE " + whereConditional;
OleDbCommand selectCmd = new OleDbCommand(sSQL, dbConn);
da.SelectCommand = selectCmd;
sSQL = "INSERT INTO " + tableName + " (";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += ") VALUES (";
for (int dci=0; dci<fields.Length; dci++) {
sSQL += "?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += "); SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (keyFields != null) {
if (keyFields.Length > 0) {
sSQL += " WHERE ";
foreach (string keyfield in keyFields) {
sSQL += "(" + keyfield + " = ?) AND ";
}
}
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
OleDbCommand insertCmd = new OleDbCommand(sSQL, dbConn);
//foreach (string field in fields) {
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter();
p.ParameterName = field;
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
insertCmd.Parameters.Add(p);
}
if (keyFields != null) {
if (keyFields.Length > 0) {
foreach (string keyfield in keyFields) {
OleDbParameter p = new OleDbParameter();
p.ParameterName = "Select_" + keyfield;
string dt = dr.GetDataTypeName(dr.GetOrdinal(keyfield));
switch (dt) {
case "String":
case "System.String":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = keyfield;
insertCmd.Parameters.Add(p);
}
}
}
da.InsertCommand = insertCmd;
sSQL = "UPDATE " + tableName + " SET ";
foreach (string field in fields) {
sSQL += field + " = ?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " WHERE ";
foreach (string field in fields) {
sSQL += field + " = ?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += "; SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (keyFields != null) {
if (keyFields.Length > 0) {
sSQL += " WHERE ";
foreach (string keyfield in keyFields) {
sSQL += "(" + keyfield + " = ?) AND ";
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
}
}
OleDbCommand updateCmd = new OleDbCommand(sSQL, dbConn);
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter();
p.ParameterName = field;
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
updateCmd.Parameters.Add(p);
}
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter("Original_" + field, OleDbType.VarChar, 20, ParameterDirection.Input, true, (byte)0, (byte)0, field, System.Data.DataRowVersion.Original, null);
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
updateCmd.Parameters.Add(p);
}
if (keyFields != null) {
if (keyFields.Length > 0) {
foreach (string keyfield in keyFields) {
OleDbParameter p = new OleDbParameter();
p.ParameterName = "Select_" + keyfield;
string dt = dr.GetDataTypeName(dr.GetOrdinal(keyfield));
switch (dt) {
case "String":
case "System.String":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = keyfield;
updateCmd.Parameters.Add(p);
}
}
}
da.UpdateCommand = updateCmd;
sSQL = "DELETE FROM " + tableName;
if (dr.FieldCount > 0) sSQL += " WHERE ";
foreach (string field in fields) {
sSQL += "(" + field + " = ?) AND ";
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
OleDbCommand deleteCmd = new OleDbCommand(sSQL, dbConn);
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter("Original_" + field, OleDbType.VarChar, 20, ParameterDirection.Input, true, (byte)0, (byte)0, field, System.Data.DataRowVersion.Original, null);
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
deleteCmd.Parameters.Add(p);
}
da.DeleteCommand = deleteCmd;
return da;
}
public static OleDbDataAdapter GenerateTableDataAdapter(string tableName, params string[] keyFields) {
return GenerateTableDataAdapter(tableName, keyFields, "");
}
Aha! Found this ... http://msdn.microsoft.com/library/en...asp?frame=true
Jon
"Jon Davis" <jo*@REMOVE.ME.PLEASE.jondavis.net> wrote in message news:Oy**************@TK2MSFTNGP12.phx.gbl...
Hm no doesn't work .. no way to debug this thing :`(
Tells me "invalid syntax near ',' " but there's no way to see the ACTUAL sql used in the update query with the ?'s. I'm confused .. and tired ..
try {
da.Update(dsUsers);
} catch (Exception ex) {
Response.Write(ex.Message + "\r\n\r\nSQL:\r\n" + da.UpdateCommand.CommandText);
Response.End();
}
"Jon Davis" <jo*@REMOVE.ME.PLEASE.jondavis.net> wrote in message news:uF**************@TK2MSFTNGP10.phx.gbl...
*sigh* Basically what I want is the ability to generate an OleDbDataAdapter on the fly, complete with its Update and Delete and Insert and Select commands readily available for automatic updates with DataSets, but while having full web.config control of the connection object.
Well, I've written up a temporary workaround. Note that this is NOT thoroughly tested, and is probably very buggy. It's a prototype, and it works good enough for me for now ... I think ...
[code follows]
public static OleDbDataAdapter GenerateTableDataAdapter(string tableName, string[] keyFields, string whereConditional) {
OleDbConnection dbConn = DbConnection;
OleDbDataReader dr = Db.ExecQueryCmd("SELECT * FROM " + tableName);
ArrayList fieldsAR = new ArrayList();
for (int f=0; f<dr.FieldCount; f++) {
fieldsAR.Add(dr.GetName(f));
}
string[] fields = (string[])fieldsAR.ToArray(typeof(string));
ArrayList al = new ArrayList();
foreach (string field in fields) {
al.Add(new System.Data.Common.DataColumnMapping(field, field));
}
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM " + tableName, dbConn);
System.Data.Common.DataColumnMapping[] mappings = (System.Data.Common.DataColumnMapping[])al.ToArray(typeof(System.Data.Common.DataColumnMa pping));
da.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "Users", mappings)});
string sSQL = "SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (whereConditional != null && whereConditional != "")
sSQL += " WHERE " + whereConditional;
OleDbCommand selectCmd = new OleDbCommand(sSQL, dbConn);
da.SelectCommand = selectCmd;
sSQL = "INSERT INTO " + tableName + " (";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += ") VALUES (";
for (int dci=0; dci<fields.Length; dci++) {
sSQL += "?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += "); SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (keyFields != null) {
if (keyFields.Length > 0) {
sSQL += " WHERE ";
foreach (string keyfield in keyFields) {
sSQL += "(" + keyfield + " = ?) AND ";
}
}
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
OleDbCommand insertCmd = new OleDbCommand(sSQL, dbConn);
//foreach (string field in fields) {
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter();
p.ParameterName = field;
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
insertCmd.Parameters.Add(p);
}
if (keyFields != null) {
if (keyFields.Length > 0) {
foreach (string keyfield in keyFields) {
OleDbParameter p = new OleDbParameter();
p.ParameterName = "Select_" + keyfield;
string dt = dr.GetDataTypeName(dr.GetOrdinal(keyfield));
switch (dt) {
case "String":
case "System.String":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = keyfield;
insertCmd.Parameters.Add(p);
}
}
}
da.InsertCommand = insertCmd;
sSQL = "UPDATE " + tableName + " SET ";
foreach (string field in fields) {
sSQL += field + " = ?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " WHERE ";
foreach (string field in fields) {
sSQL += field + " = ?, ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += "; SELECT ";
foreach (string field in fields) {
sSQL += field + ", ";
}
if (sSQL.EndsWith(", ")) sSQL = sSQL.Substring(0, sSQL.Length - 2);
sSQL += " FROM " + tableName;
if (keyFields != null) {
if (keyFields.Length > 0) {
sSQL += " WHERE ";
foreach (string keyfield in keyFields) {
sSQL += "(" + keyfield + " = ?) AND ";
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
}
}
OleDbCommand updateCmd = new OleDbCommand(sSQL, dbConn);
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter();
p.ParameterName = field;
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
updateCmd.Parameters.Add(p);
}
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter("Original_" + field, OleDbType.VarChar, 20, ParameterDirection.Input, true, (byte)0, (byte)0, field, System.Data.DataRowVersion.Original, null);
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
updateCmd.Parameters.Add(p);
}
if (keyFields != null) {
if (keyFields.Length > 0) {
foreach (string keyfield in keyFields) {
OleDbParameter p = new OleDbParameter();
p.ParameterName = "Select_" + keyfield;
string dt = dr.GetDataTypeName(dr.GetOrdinal(keyfield));
switch (dt) {
case "String":
case "System.String":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = keyfield;
updateCmd.Parameters.Add(p);
}
}
}
da.UpdateCommand = updateCmd;
sSQL = "DELETE FROM " + tableName;
if (dr.FieldCount > 0) sSQL += " WHERE ";
foreach (string field in fields) {
sSQL += "(" + field + " = ?) AND ";
}
if (sSQL.EndsWith("AND ")) sSQL = sSQL.Substring(0, sSQL.Length - 4);
OleDbCommand deleteCmd = new OleDbCommand(sSQL, dbConn);
for (int f=0; f<dr.FieldCount; f++) {
string field = dr.GetName(f);
OleDbParameter p = new OleDbParameter("Original_" + field, OleDbType.VarChar, 20, ParameterDirection.Input, true, (byte)0, (byte)0, field, System.Data.DataRowVersion.Original, null);
string dt = dr.GetDataTypeName(f);
switch (dt) {
case "String":
case "System.String":
case "VarChar":
case "OleDbType.VarChar":
p.OleDbType = OleDbType.VarChar;
break;
case "Int32":
case "System.Int32":
case "Integer":
case "OleDbType.Integer":
p.OleDbType = OleDbType.Integer;
break;
case "Boolean":
case "System.Boolean":
case "OleDbType.Boolean":
p.OleDbType = OleDbType.Boolean;
break;
case "DateTime":
case "System.DateTime":
case "DBTimeStamp":
case "OleDbType.DBTimeStamp":
p.OleDbType = OleDbType.DBTimeStamp;
break;
default:
try {
if (dt.StartsWith("DBTYPE_")) dt = dt.Substring(7);
p.OleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), dt, true);
} catch {}
break;
}
p.SourceColumn = field;
deleteCmd.Parameters.Add(p);
}
da.DeleteCommand = deleteCmd;
return da;
}
public static OleDbDataAdapter GenerateTableDataAdapter(string tableName, params string[] keyFields) {
return GenerateTableDataAdapter(tableName, keyFields, "");
} This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Simon Harvey |
last post by:
Hi all,
In my project I have made a number of helper methods static. As I understand
it, this will create the problem that multiple threads could access the
static method at the same time and...
|
by: Steven Livingstone |
last post by:
Anyone able to explain to me why you cannot define an interface that can
then be implemented using static methods?
I understand the C# CLS states this, but just interested in the reasons
behind...
|
by: Marsha |
last post by:
Does anyone know how to add a new data provider to Visual Studio - to the
Provider list in Server Explorer. I am using Sybase's ADO.NET Data Provider.
It has been added to my GAC and appears in...
|
by: Jon Davis |
last post by:
I like the drag-and-drop accessibility of dragging a table to a Web Forms
designer and seeing a SqlDataAdapter automatically created for me.. being
able to create a DataSet from that is fun and...
|
by: Simon Harvey |
last post by:
Hi all,
In my project I have made a number of helper methods static. As I understand
it, this will create the problem that multiple threads could access the
static method at the same time and...
|
by: Steen Tøttrup |
last post by:
This is what I'm doing:
I'm using Httpmodules and Httphandlers to control access to files (images,
movies, etc.), but have run into quite a problem when several files are being
requested at the...
|
by: Kevin |
last post by:
I'd like to declare my connection string as a public shared variable, to be
used throughout my ASP. Net projected (created using VB.NET 2003).
Below is the line of code:
Public Shared conStr As...
|
by: Random |
last post by:
Here's a design question I'm curious to know if anyone here has wrestled
with before...
I'm writing my data access methods in classes in the App_Code directory. I
know that I can easily...
|
by: =?Utf-8?B?cmFuZHkxMjAw?= |
last post by:
I'm working in Visual Studio 2005.
I have the following method. I'm trying to figure out why I wouldn't want to
wrap the contents in a lock(MyLockObject) {...} Seems to me that having a
static...
|
by: Dave |
last post by:
I have a global.asax file with Application_Start defined and create some
static data there and in another module used in the asp.net application and
I realize that static data is shared amongst...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |