using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
namespace SQLExample5
{
class Program
{
static string cs = "Data
Source=ssrap7;Initial Catalog=Test;Integrated Security=True";
static void Main(string[] args)
{
var dt = new DataTable();
//using
statement to ensure dispose of
using (var conn = new SqlConnection(cs))
{
try
{
string GetPersons = "SELECT * FROM Person;";
var cmnd = new SqlCommand(GetPersons, conn);
conn.Open();
var da = new SqlDataAdapter(GetPersons,
conn);
da.Fill(dt);
cmnd.Dispose();
}
catch (SqlException sqlEx)
{
//display sql exception
Console.WriteLine(sqlEx.Message);
}
catch (Exception Ex)
{
//display all other exceptions
Console.WriteLine(Ex.Message);
}
finally
{
//ensure that the connection is closed everytime
conn.Close();
}
}//dispose the connection
foreach (DataRow dr in dt.Rows)
Console.WriteLine("{0}) {1} {2} born {3}",dr[0],dr[1],dr[2],dr[3]);
}
}
}
Now that`s great if all we want to do is get some records and print them out to the user, but that`s rarely the case, generally we want to manipulate the data a bit, add, update, delete rows. Let`s make a super mega console app to work with our Person table.
Let`s create a static class called DB_Handler in the class we are going to create a reference to our connection string and let`s start by creating a function to get our data. Our function is going to return our dataAdapter with a datatable as an output paramter.
public static SqlDataAdapter GetData(string SQLcommand, out DataTable dt)
{
//Create
our Connection
using(var conn = new SqlConnection(cs))
{
try
{
SqlDataAdapter da = new SqlDataAdapter(SQLcommand, conn);
conn.Open();
dt = new DataTable();
da.Fill(dt);
return da;
}
catch(Exception ex)
{
Console.WriteLine(ex.InnerException.ToString());
}
finally
{
conn.Close();
}
}//dispose
connection
dt = new DataTable();
return null;
}
Simple enough right? Now with that complete lets create sqlCommands to insert,update and delete rows.
let's start with delete because it's the easiest.
private static SqlCommand DeletePerson()
{
var deleteCMND = new SqlCommand();
deleteCMND.CommandText = "DELETE FROM Person WHERE Id = @Id";
var dPar = new SqlParameter("@Id", SqlDbType.Int, 0, "Id");
deleteCMND.Parameters.Add(dPar);
return deleteCMND;
}
private static SqlCommand UpdatePerson()
{
var updateCMND = new SqlCommand();
updateCMND.CommandText =
@"UPDATE
PERSON SET FirstName = @fn, LastName = @ln WHERE Id = @id;";
var fnPar = new SqlParameter("@fn", SqlDbType.VarChar, 50, "FirstName");
var lnPar = new SqlParameter("@ln", SqlDbType.VarChar, 50, "LastName");
var idPar = new SqlParameter("@id", SqlDbType.Int, 0, "Id");
updateCMND.Parameters.AddRange(new SqlParameter[] {
fnPar, lnPar, idPar });
return updateCMND;
}
and finally one to insert
private static SqlCommand InsertPerson()
{
string insertCMND = "INSERT
INTO PERSON (FirstName, LastName, BirthDate) VALUES (@fn,@ln,@bd)";
SqlCommand insertCommand = new SqlCommand(insertCMND);
var fPar = new SqlParameter("@fn", SqlDbType.VarChar, 50, "FirstName");
var lPar = new SqlParameter("@ln", SqlDbType.VarChar, 50, "LastName");
var bPar = new SqlParameter("@bd", SqlDbType.Date, 50, "BirthDate");
insertCommand.Parameters.Add(fPar);
insertCommand.Parameters.Add(lPar);
insertCommand.Parameters.Add(bPar);
return insertCommand;
}
next we have to create some sort of update function for our adapter, so that we can commit our DataTable changes to our Person Table.
public static void Update(SqlDataAdapter da, DataTable dt)
{
using(var conn = new SqlConnection(cs))
try
{
conn.Open();
da.DeleteCommand = DeletePerson();
da.UpdateCommand = UpdatePerson();
da.InsertCommand = InsertPerson();
da.DeleteCommand.Connection = conn;
da.InsertCommand.Connection = conn;
da.UpdateCommand.Connection = conn;
da.Update(dt);
}
finally
{
conn.Close();
}
}
easier then you thought right? Basically all we had to do is:- pass our DataAdapter and DataTable in as paramters
- open a connection
- set the dataAdapters commands to those we defined earlier and set their connection
- then call update on our dataAdapter and pass it our data table.
pretty simple and that will save any changes that we do to our DataTable in Offline mode if you will.
Let's take a look at our entire DB_Handler Class.
public static class DB_Handler
{
static string cs = "Data
Source=ssrap7;Initial Catalog=Test;Integrated Security=True";
public static SqlDataAdapter GetData(string SQLcommand, out DataTable dt)
{
//Create
our Connection
using (var conn = new SqlConnection(cs))
{
try
{
SqlDataAdapter da = new SqlDataAdapter(SQLcommand, conn);
conn.Open();
dt = new DataTable();
da.Fill(dt);
return da;
}
catch (Exception ex)
{
Console.WriteLine(ex.InnerException.ToString());
}
finally
{
conn.Close();
}
}//dispose
connection
dt = new DataTable();
return null;
}
public static void Update(SqlDataAdapter da, DataTable dt)
{
using (var conn = new SqlConnection(cs))
try
{
conn.Open();
da.DeleteCommand =
DeletePerson();
da.UpdateCommand =
UpdatePerson();
da.InsertCommand =
InsertPerson();
da.DeleteCommand.Connection =
conn;
da.InsertCommand.Connection =
conn;
da.UpdateCommand.Connection =
conn;
da.Update(dt);
}
finally
{
conn.Close();
}
}
private static SqlCommand InsertPerson()
{
string insertCMND = "INSERT
INTO PERSON (FirstName, LastName, BirthDate) VALUES (@fn,@ln,@bd)";
SqlCommand insertCommand = new SqlCommand(insertCMND);
var fPar = new SqlParameter("@fn", SqlDbType.VarChar, 50, "FirstName");
var lPar = new SqlParameter("@ln", SqlDbType.VarChar, 50, "LastName");
var bPar = new SqlParameter("@bd", SqlDbType.Date, 50, "BirthDate");
insertCommand.Parameters.Add(fPar);
insertCommand.Parameters.Add(lPar);
insertCommand.Parameters.Add(bPar);
return insertCommand;
}
private static SqlCommand DeletePerson()
{
var deleteCMND = new SqlCommand();
deleteCMND.CommandText = "DELETE FROM Person WHERE Id = @Id";
var dPar = new SqlParameter("@Id", SqlDbType.Int, 0, "Id");
deleteCMND.Parameters.Add(dPar);
return deleteCMND;
}
private static SqlCommand UpdatePerson()
{
var updateCMND = new SqlCommand();
updateCMND.CommandText =
@"UPDATE
PERSON SET FirstName = @fn, LastName = @ln
WHERE Id = @id;";
var fnPar = new SqlParameter("@fn", SqlDbType.VarChar, 50, "FirstName");
var lnPar = new SqlParameter("@ln", SqlDbType.VarChar, 50, "LastName");
var idPar = new SqlParameter("@id", SqlDbType.Int, 0, "Id");
updateCMND.Parameters.AddRange(new SqlParameter[] {
fnPar, lnPar, idPar });
return updateCMND;
}
}
using System;
using System.Data;
using System.Data.SqlClient;
namespace SqlExampleMega
{
public static class DB_Handler
{
static string cs = "Data
Source=ssrap7;Initial Catalog=Test;Integrated Security=True";
public static SqlDataAdapter GetData(string SQLcommand, out DataTable dt)
{
//Create
our Connection
using (var conn = new SqlConnection(cs))
{
try
{
SqlDataAdapter da = new SqlDataAdapter(SQLcommand, conn);
conn.Open();
dt = new DataTable();
da.Fill(dt);
return da;
}
catch (Exception ex)
{
Console.WriteLine(ex.InnerException.ToString());
}
finally
{
conn.Close();
}
}//dispose
connection
dt = new DataTable();
return null;
}
public static void Update(SqlDataAdapter da, DataTable dt)
{
using (var conn = new SqlConnection(cs))
try
{
conn.Open();
da.DeleteCommand =
DeletePerson();
da.UpdateCommand =
UpdatePerson();
da.InsertCommand =
InsertPerson();
da.DeleteCommand.Connection =
conn;
da.InsertCommand.Connection =
conn;
da.UpdateCommand.Connection =
conn;
da.Update(dt);
}
finally
{
conn.Close();
}
}
private static SqlCommand InsertPerson()
{
string insertCMND = "INSERT
INTO PERSON (FirstName, LastName, BirthDate) VALUES (@fn,@ln,@bd)";
SqlCommand insertCommand = new SqlCommand(insertCMND);
var fPar = new SqlParameter("@fn", SqlDbType.VarChar, 50, "FirstName");
var lPar = new SqlParameter("@ln", SqlDbType.VarChar, 50, "LastName");
var bPar = new SqlParameter("@bd", SqlDbType.Date, 50, "BirthDate");
insertCommand.Parameters.Add(fPar);
insertCommand.Parameters.Add(lPar);
insertCommand.Parameters.Add(bPar);
return insertCommand;
}
private static SqlCommand DeletePerson()
{
var deleteCMND = new SqlCommand();
deleteCMND.CommandText = "DELETE FROM Person WHERE Id = @Id";
var dPar = new SqlParameter("@Id", SqlDbType.Int, 0, "Id");
deleteCMND.Parameters.Add(dPar);
return deleteCMND;
}
private static SqlCommand UpdatePerson()
{
var updateCMND = new SqlCommand();
updateCMND.CommandText =
@"UPDATE
PERSON SET FirstName = @fn, LastName = @ln
WHERE Id = @id;";
var fnPar = new SqlParameter("@fn", SqlDbType.VarChar, 50, "FirstName");
var lnPar = new SqlParameter("@ln", SqlDbType.VarChar, 50, "LastName");
var idPar = new SqlParameter("@id", SqlDbType.Int, 0, "Id");
updateCMND.Parameters.AddRange(new SqlParameter[] {
fnPar, lnPar, idPar });
return updateCMND;
}
}
class Program
{
static void Main(string[] args)
{
DataTable dt;
var da = DB_Handler.GetData("SELECT *
FROM Person", out dt);
//list
data with row index to use for maipulation
int i = 0;
foreach (DataRow dr in dt.Rows)
Console.WriteLine("{0}) {1}
{2}", i++, dr[1], dr[2]);
int selection = -1, index;
do
{
do
{
Console.WriteLine("1) Add Perosn");
Console.WriteLine("2) Update Person");
Console.WriteLine("3) Delete Person");
Console.WriteLine("0) Exit");
} while (!int.TryParse(Console.ReadLine(), out selection) &&
selection < 0 ||
selection > 3);
switch (selection)
{
case 1:
Console.WriteLine("Enter in First;Last");
string[] names = Console.ReadLine().Split(new char[] { ';' });
dt.Rows.Add(new object[] { null, names[0], names[1], new DateTime(1984, 11,
21).ToString("yyyy-MM-dd") });
Console.WriteLine(new DateTime(1984, 1,
21).ToString("yyyy-MM-dd"));
break;
case 2:
Console.WriteLine("Enter in new
index;First;Last");
string[] values = Console.ReadLine().Split(new char[] { ';' });
if (int.TryParse(values[0], out index))
{
dt.Rows[index]["FirstName"] =
values[1];
dt.Rows[index]["LastName"] =
values[2];
}
break;
case 3:
Console.WriteLine("Enter in Id to delete");
if (int.TryParse(Console.ReadLine(), out index))
dt.Rows[index].Delete();
break;
}
} while (selection != 0);
//update
our changes to our databse
DB_Handler.Update(da, dt);
}
}
}
No comments:
Post a Comment