hi rajesh,
its difficult to get you the code as i am using a
sqlhelper class. but will try to give you important
methods that i use out of sqlhelper class
my code looks like this
ds.Tables["DEPT"].Rows.Add(new object[]{"200","EDI"});
ds.Tables["DEPT"].Rows.Add(new
object[]{"201","mdi"});
ds.Tables["EMP"].Rows.Add(new
object[]{"234","chris","200","F"});
SqlCommand insertCommand =
SqlHelper.CreateCommand(
connection, "addDept", "deptID", "deptNAME" );
SqlCommand updateCommand =
SqlHelper.CreateCommand(
connection, "updatedept", "deptID", "deptName");
SqlCommand deleteCommand =
SqlHelper.CreateCommand(
connection, "deletedept", "deptID" );
SqlHelper.UpdateDataset
(insertCommand,deleteCommand,updateCommand,ds,"DEPT");
SqlCommand insertCommand1
= SqlHelper.CreateCommand(
connection, "addEMP", "EMPID", "EMPNAME","DEPTID","GENDER"
);
SqlCommand updateCommand1
= SqlHelper.CreateCommand(
connection, "updateEMP", "EmpID", "EMPName","DEPTID","GENDE
R" );
SqlCommand deleteCommand1
= SqlHelper.CreateCommand(
connection, "deleteEMP", "EMPID" );
SqlHelper.UpdateDataset
(insertCommand1,deleteCommand1,updateCommand1,ds,"EMP");
sqlHelper.createCommand looks like this
public static SqlCommand CreateCommand(SqlConnection
connection, string spName, params string[] sourceColumns)
{
if( connection == null ) throw new
ArgumentNullException( "connection" );
if( spName == null ||
spName.Length == 0 ) throw new ArgumentNullException
( "spName" );
// Create a SqlCommand
SqlCommand cmd = new SqlCommand( spName,
connection );
cmd.CommandType = CommandType.StoredProcedure;
// If we receive parameter values, we need to
figure out where they go
if ((sourceColumns != null) &&
(sourceColumns.Length > 0))
{
// Pull the parameters for this stored
procedure from the parameter cache (or discover them &
populate the cache)
SqlParameter[] commandParameters =
SqlHelperParameterCache.GetSpParameterSet(connection,
spName);
// Assign the provided source columns to
these parameters based on parameter order
for (int index=0; index <
sourceColumns.Length; index++)
commandParameters[index].SourceColumn
= sourceColumns[index];
// Attach the discovered parameters to the
SqlCommand object
AttachParameters (cmd, commandParameters);
}
return cmd;
}
and finally
sqlHelper.UpdateDataset looks like this
public static void UpdateDataset(SqlCommand
insertCommand, SqlCommand deleteCommand, SqlCommand
updateCommand, DataSet dataSet, string tableName)
{
if( insertCommand == null ) throw
new ArgumentNullException( "insertCommand" );
if( deleteCommand == null ) throw
new ArgumentNullException( "deleteCommand" );
if( updateCommand == null ) throw
new ArgumentNullException( "updateCommand" );
if( tableName == null ||
tableName.Length == 0 ) throw new ArgumentNullException
( "tableName" );
// Create a SqlDataAdapter, and dispose of it
after we are done
using (SqlDataAdapter dataAdapter = new
SqlDataAdapter())
{
// Set the data adapter commands
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
// Update the dataset changes in the data
source
dataAdapter.Update (dataSet, tableName);
// Commit all the changes made to the
DataSet
dataSet.AcceptChanges();
}
}
//
there are totally six procs 3 for dept and 3 for emp
one each for insert, update and delete
yes they all work fine if they are used single at a time
hope this helps
thanks and regards
-----Original Message-----
would like to see your coding for "insertcommand" and "insertcommand1" and
the parameters of both stored procedures.
also, make sure your stored procedure works fine.
Rajesh Patel
Hi all,
my code looks something like this
connection = createconnection() // creates connection
SqlDataAdapter ad=new sqlAdatpter("select * from
EMP",connection);
SqlDataAdapter ad1=new sqlAdatpter("select * from
DEPT",connection);
DataSet ds=new Dataset();
ad.fill(ds,"EMP");
ad1.fill(ds,"DEPT");
ds.Tables["DEPT"].Rows.Add(new object[] {"200","EDI"});
ds.Tables["DEPT"].Rows.Add(new object[] {"201","mdi"});
ds.Tables["EMP"].Rows.Add(new object[]
{"234","chris","200","F"});
SQlCommand insertcommand=cretaeInsertCommand();// call a
stored proc to insert into dept table
sqlDataAdapter Myadapter=new sqlDataAdapter();
Myadapter.connection=connection;
myadapter.insertcommand=insertcommand;
myadaper.update(ds,"dept");
SQlCommand insertcommand1=cretaeInsertCommand();// call
a stored proc to insert into emp table
myadapter.insertcommand=insertcommand1;
myadaper.update(ds,"emp");
here my problem is it inserts dept table with two records
but doesn't insert any record in emp table
what do u think must be the problem?
thanks in advance
regards
shiv
.
