ASP.NET - Querry

Asked By myzonal.com myzonal.com on 15-Feb-11 07:40 AM
Hi,

how we insert data in three table in same time ???
Subhashini Janakiraman replied to myzonal.com myzonal.com on 15-Feb-11 07:50 AM
Using Begin() and Commit() of the Transaction Object we can insert data for 3 tables at the same time.

OleDbConnection con=new OleDbConnection("....");
con.Open();

OleDbTransaction trans = dat.con.BeginTransaction();

OleDbCommand com1=new OleDbCommand("Query1",con);
OleDbCommand com2=new OleDbCommand("Query2",con);
OleDbCommand com3=new OleDbCommand("Query3",con);
trans.Begin();
com1.ExecuteNonQuery();
com2.ExecuteNonQuery();
com3.ExecuteNonQuery();
trans.Commit();
con.Close();

Mihir Soni replied to myzonal.com myzonal.com on 15-Feb-11 07:57 AM
Hello,

If you are developing your application using 3-tier architecture then write stored procedure as follow,

CREATE PROCEDURE Registration.GetStudentIdentification
@fname varchar(25),
@lname varchar(25),


@contact varchar(25)


AS
BEGIN
insert into firsttable values (@fname,@lname,@contact)
    insert into secondtable values(@fname,@lname,@contact)
    insert into thirdtable values (@fname,@lname,@contact)

END
GO

And if you are using SQL queries in your cs page itself then write insert query one after another.

Thank you.
Reena Jain replied to myzonal.com myzonal.com on 15-Feb-11 08:00 AM
hi,

you can use stored procedure to insert the data in three table simultaneously. just put all your three insert query one by one in stored procedure and call this stored procedure in aspx.cs file like this

protected void Button1_Click(object sender, EventArgs e)
 
{
   con = new SqlConnection("server=(local); database= gaurav;uid=sa;pwd=");
   cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = TextBox1.Text;
   cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = TextBox2.Text;
   cmd.Parameters.Add("@ConfirmPassword", SqlDbType.VarChar).Value = TextBox3.Text;
   cmd.Parameters.Add("@EmailID", SqlDbType.VarChar).Value = TextBox4.Text;
   cmd = new SqlCommand("submitrecord", con);
   cmd.CommandType = CommandType.StoredProcedure;
   con.Open();
   cmd.ExecuteNonQuery();
   con.Close();
}
}

hope this will help you
Daivagna Nanavati replied to myzonal.com myzonal.com on 15-Feb-11 12:04 PM
Hi

SPs are ment for that, where you can write multiple statements at a time, and when you use transaction inside it, it will mkae sure that the transaction is ATOMIC, so that either all the tables would get updates with data or none, and you can call that SP from front end like following

SqlConnection con = new SqlConnection("<your connection string");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "sp_Insert_Tables";
cmd.Connection = con;
cmd.CommandType = CommandType.StoreProcedure;
  
 
try
{
   con.Open();
   cmd.ExecuteNonQuery();
   con.Close();
}
catch(Exception ex)
{
   con.Close();
}

and you Store procedure would be like following

ALTER PROCEDURE [dbo].[sp_Insert_Tables]
  <your paramters>
 
AS
BEGIN
 BEGIN TRY
  BEGIN TRAN
  INSERT INTO Table1 VALUES(...)
  INSERT INTO Table2 VALUES(...)
  INSERT INTO Table3 VALUES(...)
  COMMIT TRAN
 END TRY
 BEGIN CATCH
  ROLLBACK
 END CATCH
END

this is how you would go ahead

let me know

Thanks
Anoop S replied to myzonal.com myzonal.com on 16-Feb-11 12:37 AM
You can only insert into one table at a time. The SQL statement "INSERT" in any variant of SQL, only allows one table to be specified. however, You can include multiple statements in a single query; separate the statement with a semi-colon.