Database Manipulation

When dealing some programs when we learn the basic we want more advance knowledge that we want to gain for me an interesting part in programming is when we connect onto a database.

Things to consider when doing some database manipulation (C# using oledb)

1.       Initialize some connection on your databse  (OledbConnection)

2.       Create a sql command to execute(OledbCommand)

3.       And if when reading record use a reader(OledbDataReader)

That is the sequence that we need to understand when dealing with database  on c# first is to establish our connection then after connecting create a oledbcommand that will use this connection and if you are retrieving a record from the database use a Datareader this will read all of the record from the start to bottom.

Suppose that you already created a database and Stored procedure on your sql server version 2005 do the following code.

Create a separate class on your visual c# project call it cls_DBManipulation

Now first thing first we need to include the following library on your class just place this line of code on the top of your class using System.Data.OleDb; Oledb is used when we need to have a more flexible way of coding its supports all of the types of database meaning migration will be easy just change the connection string then your donem, now were ready to code

OleDbConnection con = new OleDbConnection();

        private void init_con()

        {

            if (con.State == System.Data.ConnectionState.Closed())

            {

                con.ConnectionString = "";

                con.Open();

            }

       

        }

After the first open curly bracket of your class I declared a varible named con this is a OledbConnection that will in able as to connect on the database, then weve created a function init_con. The fist line of code is doing some checking if the connection already open, the con.connectionstring needs to be assinged in order for us to connect to the database.

Procedures on Getting a connection string

1.  Create a notepad then save that text document having udl

2.  Double click the file there must be dialog that will open

3.  Select a provider for this project it uses  sql 2005 so we will use this provider Microsoft OLE DB provider for sql server

4.  Then after that put the name of your database server

5.  Then after that check the radio botton Use windows NT Integrated security then press ok

6.  The open the file by using notepad then copy the connection string

After copying the connection string just paste the connection string on Con.connectionstring then enclose it with double quote and add one backslash because backslash is a special character in c#.

   public void insert_record(string fname, string lname, string mname, string address)

        {

            int id = getID();

            init_con();

            OleDbCommand cmd = con.CreateCommand();

            cmd.CommandText = "insert_rec";

            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@id", OleDbType.Integer)=id;

            cmd.Parameters.Add("@fname", OleDbType.VarChar).Value= fname;

            cmd.Parameters.Add("@lname", OleDbType.VarChar).Value = lname;

            cmd.Parameters.Add("@mname",OleDbType.VarChar ).Value=mname;

            cmd.Parameters.Add("@address", OleDbType.VarChar).Value = address;

            cmd.ExecuteNonQuery();

            con.Close();

        }

This function is created to call a stored procedure named insert_rec the fitst code declare a varible named id and it call a funtion getID then after that is to call the method on openning a connection, after opening the connection the creation of command is done it implies that create a command then use the con as connection.

      The next part is to declare to assign the command text(the name of the stored procedure) and we need to assign the type of the command to be stored procedure, the creation of insert rec requires a paramater the assignmen to of parameter is done using the code after the command type take note that the name of the paramater and datatype should match to your stored procedure, to execute the code you need to do cmd.ExecuteNonquery if this one is not included on your code there will be no changes to you database.

       private int getID()

        {  

           

            updateID();
            OleDbCommand cmd = con.CreateCommand();
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "getid";
            int id = (int)cmd.ExecuteScalar();
    
            return id;

       

        }

      Since we already called init_con we don’t need to call it again the only deffirence of this code is to the other function is just it use ExecuteScallar() is you are using sql statement that returns only one value the use this code.

   private void updateID()

        {

            OleDbCommand cmdUpID = con.CreateCommand();
            cmdUpID.CommandType = System.Data.CommandType.StoredProcedure;

            cmdUpID.CommandText = "update_id";
            cmdUpID.ExecuteNonQuery();

        }

the rest is a challenge for you to do such us deleting, searching and modifying a record

-------------------------------------------------------------------------

SQL PART

getID body have the following code

Select ID from tbl_ID

Insert_rec body have the following code

Insert into tbl_info(ID,Fname,Lname,Mname,address)values(@ID,@fname,@lname,@mname,@address)

Update_id body have the following code

Update tbl_ID set ID = ID +1