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 updateMatrixData(System.Data.DataTable dt)
{
DataAccessor.cmDABOMatrix insMatrix = new DataAccessor.cmDABOMatrix();
DataAccessor.DataClass dataC = new DataAccessor.DataClass();
this.sqlConnectionString = dataC.sqlConnectionString;
SqlConnection scnn = new SqlConnection(this.sqlConnectionString);
SqlCommand scmd = new SqlCommand();
try
{
scnn.Open();
scmd.Connection = scnn;
scmd.CommandType = CommandType.Text;
scmd.CommandText = "BEGIN TRAN";
scmd.ExecuteNonQuery();

insMatrix.sqlConn = scnn;
insMatrix.updateMatrixData(dt);

scmd.CommandType = CommandType.Text;
scmd.CommandText = "COMMIT TRAN";
scmd.ExecuteNonQuery();

}
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 updateMatrixData(System.Data.DataTable dt)
{
SqlCommand cmd = new SqlCommand();
SqlDataAdapter sDA = new SqlDataAdapter();
DataTable dti = dt.GetChanges(DataRowState.Added);
DataTable dtc = dt.GetChanges(DataRowState.Modified);
DataTable dtd = dt.GetChanges(DataRowState.Deleted);

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.StoredProcedure;
cmd.Parameters.Add(sDA);
cmd.CommandText = "spUpdateMatrixData";
cmd.ExecuteNonQuery();
}
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
[e-mail]