Updating a SQL Server Table using a datatable and stored procedure

[LoanB]

Hey guys need some info here.

I am adding, updating and deleting data on a datagrid. I pass the datagrid's datasource (datatable dt) to my data layer.

Assuming I have already created and opened a connection in a business layer and passed it to my datalayer I am now stuck here:

My business layer code is as follows:

//UPDATE MATRIX STOCK DATA TO SQL DATABASE
public void updateMatrixDat a(System.Data.D ataTable dt)
{
DataAccessor.cm DABOMatrix insMatrix = new DataAccessor.cm DABOMatrix();
DataAccessor.Da taClass dataC = new DataAccessor.Da taClass();
this.sqlConnect ionString = dataC.sqlConnec tionString;
SqlConnection scnn = new SqlConnection(t his.sqlConnecti onString);
SqlCommand scmd = new SqlCommand();
try
{
scnn.Open();
scmd.Connection = scnn;
scmd.CommandTyp e = CommandType.Tex t;
scmd.CommandTex t = "BEGIN TRAN";
scmd.ExecuteNon Query();

insMatrix.sqlCo nn = scnn;
insMatrix.updat eMatrixData(dt) ;

scmd.CommandTyp e = CommandType.Tex t;
scmd.CommandTex t = "COMMIT TRAN";
scmd.ExecuteNon Query();

}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
scnn.Close();
scnn.Dispose();
insMatrix = null;
}
}


My data layer code is as follows:

public void updateMatrixDat a(System.Data.D ataTable dt)
{
SqlCommand cmd = new SqlCommand();
SqlDataAdapter sDA = new SqlDataAdapter( );
DataTable dti = dt.GetChanges(D ataRowState.Add ed);
DataTable dtc = dt.GetChanges(D ataRowState.Mod ified);
DataTable dtd = dt.GetChanges(D ataRowState.Del eted);

string col1, col2, col3, col4, col5, col6, col7;
char mode;
int id;

try
{

// DOnt know how to do this
sDA.Update(dti) ;



cmd.Connection = this.sqlConn;
cmd.CommandType = CommandType.Sto redProcedure;
cmd.Parameters. Add(sDA);
cmd.CommandText = "spUpdateMatrix Data";
cmd.ExecuteNonQ uery();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}


I know the data layer is incorrect. any idea how I can update, add and delete?

The stored procedure accepts a mode char I = Insert, D = Delete and U = update.

it also accepts a parameter for each field heading @col1 through to @ col7

Please help!!!1

Thanks,

Lóan
loan.burger@gma il.com