Static shared data connection provider?

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

  1. Jon Davis

    Jon Davis Guest

    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
     
    Jon Davis, Apr 3, 2004
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:%...
    >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
    >
    >
     
    Ken Cox [Microsoft MVP], Apr 4, 2004
    #2
    1. Advertising

  3. Jon Davis

    Jon Davis Guest

    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]" <> wrote in message
    news:%...
    > 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" <> wrote in message
    > news:%...
    > >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
    > >
    > >

    >
     
    Jon Davis, Apr 4, 2004
    #3
  4. 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" <> wrote in message
    news:...
    > 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]" <> wrote in message
    > news:%...
    > > 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" <> wrote in message
    > > news:%...
    > > >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
    > > >
    > > >

    > >

    >
    >
     
    William \(Bill\) Vaughn, Apr 4, 2004
    #4
  5. Jon Davis

    Jon Davis Guest

    *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
    #5
  6. Jon Davis

    Jon Davis Guest

    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
    #6
  7. 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
    #7
    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,074
    Alvin Bruney [MVP]
    Jun 17, 2004
  2. Jon Davis
    Replies:
    0
    Views:
    442
    Jon Davis
    Apr 6, 2004
  3. sloan
    Replies:
    5
    Views:
    1,519
    sloan
    Jun 4, 2006
  4. tropos
    Replies:
    3
    Views:
    472
  5. Jon Davis

    Static shared data connection provider?

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

Share This Page