423,818 Members | 2,250 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,818 IT Pros & Developers. It's quick & easy.

Updating a SQL Server Table using a datatable and stored procedure

P: 62
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,

Lan
loan.burger@gmail.com
Apr 2 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.