none
C# 3.0 - Write to MS Access or SQL Server database in a multiuser environment RRS feed

  • Question

  • Hi guys,

    I need to create an application that reads from and writes to a database in a multiuser environment.

    I am having problems to understand what I should do with the part of the application that writes to mydb. I don't even know where to start.

    Do I need to implement a semaphore to enable writing to the db? (If yes, do you have any example?).

    Do I need to create a TcpClient connection?

    Do I just try to write to the database (using the code below) letting the database program (Access / SQL Server) managing everything?

    Please take in consideration that I am just a beginner.

    Thanks in advance for any help.

    Aldo.

    Code Snippet

            // Update data to my MS Access Table

            private static void UpdateTable()

            {

                string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=C:\...\myDB.accdb";

     

                // SQL command

                string sSQLCommand1 = "DROP TABLE myTable";

                string sSQLCommand2 = "CREATE TABLE myTable (ID AutoIncrement, myID int NOT NULL, Name VarChar(40) NOT NULL)";

                string sSQLCommand3 = "INSERT INTO myTable (myID, Name) VALUES (4, 'my name')";

                string sSQLCommand4 = "UPDATE myTable SET Name = 'Updated Name' WHERE myID = 4";

                string sSQLCommand5 = "DELETE FROM myTable WHERE Name = 'Updated Name'";

                // Create the connection

                using (OleDbConnection myConnection = new OleDbConnection(conn))

                {

                    // Create the command object

                    using (OleDbCommand cmmd = new OleDbCommand(sSQLCommand1, myConnection))

                    {

                        myConnection.Open();

                        // Execute the SQL command

                        cmmd.ExecuteNonQuery();

                        myConnection.Close();

                    }

                }

            }

     

     

    Sunday, April 27, 2008 2:25 PM

Answers

  • To my understanding in .net Semaphores is something related to multi threading.

    TCPClient is not used for database related operation. 

     

    If you want to provide your application to support different databases like sql,access,oracle etc...you can take of .Net 2.0 and use dbprovider factory.

     

    http://www.simple-talk.com/dotnet/.net-framework/ado.net-2.0-factory-classes/

     

    This may help.

    Monday, April 28, 2008 8:50 AM
  • Hi guys,

     

    Below the solution I made following the suggestions in:

    http://eggheadcafe.com/community/aspnet/2/10034233/c-30--how-to-manage-wr.aspx

     

    If there is anyway to improve it please let me know.

     

    Thanks,

    Aldo.

     

    Code Snippet

    Configuration file

     

    <?xml version="1.0" encoding="utf-8" ?>

    <configuration>

      <connectionStrings>

        <add

          name ="msAccessConnStr"

          connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Visual Studio 2008\Projects\Exercises\WriteToMsAccessDb\DB\myDB.mdb"

          />

      </< SPAN>connectionStrings>

    </< SPAN>configuration>

     

     

    Code Snippet

            ///

            /// The following method updates data using parameterized query.

            /// The Provider and Connection string are retrieved from the project's configuration file "App.config"

            ///

            /// In order to Write the connection string to App.config do:

            /// // get the config file for this application

            /// Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);

            /// // set the new values

            /// config.ConnectionStrings.ConnectionStrings["Connection Name"].ConnectionString = "Connection String Value";

            /// // save and refresh the config file

            /// config.Save( ConfigurationSaveMode.Minimal );

            /// ConfigurationManager.RefreshSection( "connectionStrings" );

            ///

            void UpdateDataBase()

            {

                // Get Provider and Connection string from this Project Configuration file (App.config)

                string conn = ConfigurationManager.ConnectionStrings["msAccessConnStr"].ConnectionString;

                // In case I need to write the connection string use: string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=E:\Visual Studio 2008\Projects\Exercises\WriteToMsAccessDb\DB\myDB.mdb";

                

                // Examples for SQL commands

                //string sSQLCommand1 = "DROP TABLE myTable1";

                //string sSQLCommand2 = "CREATE TABLE myTable1 (ID AutoIncrement, myID int NOT NULL, Name VarChar(40) NOT NULL)";

                //string sSQLCommand3 = "INSERT INTO myTable1 (myID, Name) VALUES (4, 'my name')";

                //string sSQLCommand4 = "UPDATE myTable1 SET Name = 'Updated Name' WHERE myID = 4";

                //string sSQLCommand5 = "DELETE FROM myTable1 WHERE Name = 'Updated Name'";

     

                // SQL Command line

                string sSQLCommand6 = "UPDATE myTable1 SET Notes = 'Updated' WHERE FirstName = ? OR LastName = ?";

               

                // Create the connection

                using (OleDbConnection myConnection = new OleDbConnection(conn))

                {

                    // Create the command object

                    using (OleDbCommand cmd = new OleDbCommand(sSQLCommand6, myConnection))

                    {

                        cmd.CommandTimeout = 15;

                        cmd.CommandType = CommandType.Text;

                        cmd.Parameters.AddWithValue("FirstName", txtCondition1.Text);

                        cmd.Parameters.AddWithValue("LastName", txtCondition2.Text);

                       

                        try

                        {

                            myConnection.Open();

                            // Execute the SQL command

                            cmd.ExecuteNonQuery();

                            myConnection.Close();

                        }

                        catch (Exception ex)

                        {

                            MessageBox.Show("Error: " + ex.Message, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                            Application.Exit(); // finish the program

                        }

                    }

                }

            }

     

        }

    }

     

     

    Wednesday, April 30, 2008 1:13 PM

All replies

  • To my understanding in .net Semaphores is something related to multi threading.

    TCPClient is not used for database related operation. 

     

    If you want to provide your application to support different databases like sql,access,oracle etc...you can take of .Net 2.0 and use dbprovider factory.

     

    http://www.simple-talk.com/dotnet/.net-framework/ado.net-2.0-factory-classes/

     

    This may help.

    Monday, April 28, 2008 8:50 AM
  • Hi guys,

     

    Below the solution I made following the suggestions in:

    http://eggheadcafe.com/community/aspnet/2/10034233/c-30--how-to-manage-wr.aspx

     

    If there is anyway to improve it please let me know.

     

    Thanks,

    Aldo.

     

    Code Snippet

    Configuration file

     

    <?xml version="1.0" encoding="utf-8" ?>

    <configuration>

      <connectionStrings>

        <add

          name ="msAccessConnStr"

          connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Visual Studio 2008\Projects\Exercises\WriteToMsAccessDb\DB\myDB.mdb"

          />

      </< SPAN>connectionStrings>

    </< SPAN>configuration>

     

     

    Code Snippet

            ///

            /// The following method updates data using parameterized query.

            /// The Provider and Connection string are retrieved from the project's configuration file "App.config"

            ///

            /// In order to Write the connection string to App.config do:

            /// // get the config file for this application

            /// Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);

            /// // set the new values

            /// config.ConnectionStrings.ConnectionStrings["Connection Name"].ConnectionString = "Connection String Value";

            /// // save and refresh the config file

            /// config.Save( ConfigurationSaveMode.Minimal );

            /// ConfigurationManager.RefreshSection( "connectionStrings" );

            ///

            void UpdateDataBase()

            {

                // Get Provider and Connection string from this Project Configuration file (App.config)

                string conn = ConfigurationManager.ConnectionStrings["msAccessConnStr"].ConnectionString;

                // In case I need to write the connection string use: string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=E:\Visual Studio 2008\Projects\Exercises\WriteToMsAccessDb\DB\myDB.mdb";

                

                // Examples for SQL commands

                //string sSQLCommand1 = "DROP TABLE myTable1";

                //string sSQLCommand2 = "CREATE TABLE myTable1 (ID AutoIncrement, myID int NOT NULL, Name VarChar(40) NOT NULL)";

                //string sSQLCommand3 = "INSERT INTO myTable1 (myID, Name) VALUES (4, 'my name')";

                //string sSQLCommand4 = "UPDATE myTable1 SET Name = 'Updated Name' WHERE myID = 4";

                //string sSQLCommand5 = "DELETE FROM myTable1 WHERE Name = 'Updated Name'";

     

                // SQL Command line

                string sSQLCommand6 = "UPDATE myTable1 SET Notes = 'Updated' WHERE FirstName = ? OR LastName = ?";

               

                // Create the connection

                using (OleDbConnection myConnection = new OleDbConnection(conn))

                {

                    // Create the command object

                    using (OleDbCommand cmd = new OleDbCommand(sSQLCommand6, myConnection))

                    {

                        cmd.CommandTimeout = 15;

                        cmd.CommandType = CommandType.Text;

                        cmd.Parameters.AddWithValue("FirstName", txtCondition1.Text);

                        cmd.Parameters.AddWithValue("LastName", txtCondition2.Text);

                       

                        try

                        {

                            myConnection.Open();

                            // Execute the SQL command

                            cmd.ExecuteNonQuery();

                            myConnection.Close();

                        }

                        catch (Exception ex)

                        {

                            MessageBox.Show("Error: " + ex.Message, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                            Application.Exit(); // finish the program

                        }

                    }

                }

            }

     

        }

    }

     

     

    Wednesday, April 30, 2008 1:13 PM