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
