OLEDB Database connection string in C#.Net


OledDb database connections allow C# developers to build data powered applications with Access database. It can be possible with Database connection object and command object.

Connection string 

For connecting .mdb Access files you can use the following connection string,
string connstr = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/.Net Project/PharmaERP/PharmaERP/PharmaData.mdb”;
and for the Access 2007, 2012,2016 databases you have to use the following one.          

string connstr = “Provider=Microsoft.ACE.OLEDB.12.0;;Data Source=D:/.Net Project/PharmaERP/pharmaData.accdb;Jet OLEDB:Database Password=password”;

 Now we are ready to setup the connection, let build the connection objects and get started.

Connecting database with OleDbConnection object.

     OleDbConnection  conn;

     OleDbCommand Rs;

      public void Connect()
        {
            conn = new OleDbConnection(connstr);
            conn.Open(); 
        }
With the OleDbConnection object, we are ready to establish connection with the data base, all we need to manipulate data is Command object. Let start with Insertion command first.
We can use insert into SQL statement and execute, DML command with ExecuteNonquery() of command object. We can also use the Parameter to pass arguments to the insert statements. Lets have a look at the code.
 

public void New_Medicines(Product_Registration f)

        {
               Connect();
                Rs = new OleDbCommand(“insert into new_Medicines(product_name,pdate,manufacture,catagory,subcatagory,type,packing,printpack,reorderlevel,tax,ptr,pts,contents,rackno) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)”, conn);
                Rs.Parameters.Add(new OleDbParameter(“product_name”, f._txtPname));
                Rs.Parameters.Add(new OleDbParameter(“pdate”,f._txtPrdate ));
                Rs.Parameters.Add(new OleDbParameter(“manufacture”, f._txtmanufacture));
                Rs.Parameters.Add(new OleDbParameter(“catagory  “, f._txtCatagory));
                Rs.Parameters.Add(new OleDbParameter(“subcatagory”, f._txtSubCatagory));
                Rs.Parameters.Add(new OleDbParameter(“type”, f._txtType));
                Rs.Parameters.Add(new OleDbParameter(“packing”, f._txtPackNo));
                Rs.Parameters.Add(new OleDbParameter(“printpack”, f._txtPackPrint));
                Rs.Parameters.Add(new OleDbParameter(“reorderlevel”, f._txtReorderLevel));
                Rs.Parameters.Add(new OleDbParameter(“tax”, f._txtTax));
                Rs.Parameters.Add(new OleDbParameter(“ptr”, f._txtPTR));
                Rs.Parameters.Add(new OleDbParameter(“pts”, f._txtPTS));
                Rs.Parameters.Add(new OleDbParameter(“contents”, f._txtContents));
                Rs.Parameters.Add(new OleDbParameter(“rackno”, f._txtRack));
                Rs.ExecuteNonQuery();
        }

The command object is also capable of running queries and join statements. Try it yourself.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.