Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Re: Static shared data connection provider?

Reply
Thread Tools

Re: Static shared data connection provider?

 
 
Jon Davis
Guest
Posts: n/a
 
      04-06-2004
Aha! Found this ...

http://msdn.microsoft.com/library/en...asp?frame=true

Jon

"Jon Davis" <(E-Mail Removed)> wrote in message news:...
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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
*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, "");
}
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cpp + Python: static data dynamic initialization in *nix shared lib? Alf P. Steinbach /Usenet C++ 10 07-13-2010 09:28 PM
Should Data Classes be Shared/Static Assemblies? Random ASP .Net 2 12-20-2006 05:53 PM
Problem: shared object loading runs constructor of a static object, but static linkage does not. tropos C++ 3 11-30-2005 04:54 PM
Static shared data connection provider? Jon Davis ASP .Net Datagrid Control 6 04-06-2004 01:48 AM
Static shared data connection provider? Jon Davis ASP .Net 6 04-06-2004 01:48 AM



Advertisments