Re: Static shared data connection provider?

Discussion in 'ASP .Net' started by Jon Davis, Apr 6, 2004.

  1. Jon Davis

    Jon Davis Guest

    Aha! Found this ...

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

    Jon

    "Jon Davis" <> 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" <> wrote in message news:...
    *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.DataColumnMapping));
    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, "");
    }
    Jon Davis, Apr 6, 2004
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. BS
    Replies:
    3
    Views:
    2,042
    Alvin Bruney [MVP]
    Jun 17, 2004
  2. Jon Davis

    Static shared data connection provider?

    Jon Davis, Apr 3, 2004, in forum: ASP .Net
    Replies:
    6
    Views:
    364
    Jon Davis
    Apr 6, 2004
  3. sloan
    Replies:
    5
    Views:
    1,494
    sloan
    Jun 4, 2006
  4. tropos
    Replies:
    3
    Views:
    447
  5. Jon Davis

    Static shared data connection provider?

    Jon Davis, Apr 3, 2004, in forum: ASP .Net Datagrid Control
    Replies:
    6
    Views:
    133
    Jon Davis
    Apr 6, 2004
Loading...

Share This Page