Tag Archives: select

29 Mar

Select,Insert,Update,Delete Data in Access File using C#

This example shows how to insert ,update, delete and select data in Access File(CRUD Operations).

TABLE1:

ID NAME SURNAME
1 Jack Sparrow

Select Command Example:

            
            string mdfFile = @"csharpexamples.mdb";

            using (OleDbConnection connection = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", mdfFile)))
            {
                using (OleDbCommand selectCommand = new OleDbCommand("SELECT TOP 10 * FROM TABLE1", connection))
                {
                    connection.Open();

                    DataTable table = new DataTable();
                    OleDbDataAdapter adapter = new OleDbDataAdapter();
                    adapter.SelectCommand = selectCommand;
                    adapter.Fill(table);

                    foreach (DataRow row in table.Rows)
                    {
                        object nameValue = row["NAME"];
                        object surnameValue = row["SURNAME"];
                    }
                }
            }

Insert Command Example:

            
            string mdfFile = @"csharpexamples.mdb";

            using (OleDbConnection connection = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", mdfFile)))
            {
                using (OleDbCommand insertCommand = new OleDbCommand("INSERT INTO TABLE1 ([NAME],[SURNAME]) VALUES (?,?)", connection))
                {
                    connection.Open();

                    insertCommand.Parameters.AddWithValue("@NAME", "Brad");
                    insertCommand.Parameters.AddWithValue("@SURNAME", "Pitt");

                    insertCommand.ExecuteNonQuery();
                }
            }

Update Command Example:

      
            string mdfFile = @"csharpexamples.mdb";

            using (OleDbConnection connection = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", mdfFile)))
            {
                using (OleDbCommand updateCommand = new OleDbCommand("UPDATE TABLE1 SET [NAME] = ?, [SURNAME] = ? WHERE [ID] = ?", connection))
                {
                    connection.Open();

                    updateCommand.Parameters.AddWithValue("@NAME", "Brad2");
                    updateCommand.Parameters.AddWithValue("@SURNAME", "Pitt2");
                    updateCommand.Parameters.AddWithValue("@ID", 2);

                    updateCommand.ExecuteNonQuery();
                }
            }

Delete Command Example:

            
            string mdfFile = @"csharpexamples.mdb";

            using (OleDbConnection connection = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", mdfFile)))
            {
                using (OleDbCommand deleteCommand = new OleDbCommand("DELETE FROM TABLE1 WHERE [ID] = ?", connection))
                {
                    connection.Open();

                    deleteCommand.Parameters.AddWithValue("@ID", 2);

                    deleteCommand.ExecuteNonQuery();
                }
            }

See Also: Select,Insert,Update,Delete Data in MySQL using C#

01 Haz

Select,Insert,Update,Delete Data in MySQL using C#

This example shows how to insert ,update, delete and select data in MySQL.
Firstly, you should install MySql Data Connector program.
https://dev.mysql.com/downloads/connector/net/6.9.html
Then, we need to add a reference to the MySyql.Data

  • Go to solution explorer of your project
  • Select add a reference
  • Click on .Net Tab
  • Select MySql.Data

Add Reference

Select Command Example:

            string connectionString = @"server=localhost;userid=user1;password=12345;database=mydb";

            MySqlConnection connection = null;
            MySqlDataReader reader = null;
            try
            {
                connection = new MySqlConnection(connectionString);
                connection.Open();

                string stm = "SELECT * FROM Customers";
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter();
                dataAdapter.SelectCommand = new MySqlCommand(stm, connection);
                DataTable table = new DataTable();
                dataAdapter.Fill(table);
                return table;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                if (connection != null)
                    connection.Close();
            }

Insert Command Example:

            string connectionString = @"server=localhost;userid=user1;password=12345;database=mydb";

            MySqlConnection connection = null;
            try
            {
                connection = new MySqlConnection(connectionString);
                connection.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = connection;
                cmd.CommandText = "INSERT INTO Customers(Name) VALUES(@Name)";
                cmd.Prepare();

                cmd.Parameters.AddWithValue("@Name", "Bill Gates");
                cmd.ExecuteNonQuery();    
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

Update Command Example:

            string connectionString = @"server=localhost;userid=user1;password=12345;database=mydb";

            MySqlConnection conn = null;
            MySqlTransaction tr = null;

            try
            {
                conn = new MySqlConnection(connectionString);
                conn.Open();
                tr = conn.BeginTransaction();

                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                cmd.Transaction = tr;

                cmd.CommandText = "UPDATE Customers SET Name='Bill Gates' WHERE Id=1";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "UPDATE Books SET Title='New Title' WHERE Id=2";
                cmd.ExecuteNonQuery();

                tr.Commit();

            }
            catch (MySqlException ex)
            {
                tr.Rollback();
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }

Delete Command Example:

            string connectionString = @"server=localhost;userid=user1;password=12345;database=mydb";
            MySqlConnection connection = null;
            try
            {
                connection = new MySqlConnection(connectionString);
                connection.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = connection;
                cmd.CommandText = "delete from Customers where ID='" + "1" + "';";
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

See Also: Select,Insert,Update,Delete Data in Access File using C#