asp.net的事务写法(菜鸟有礼貌 回答100分)

Question

using (conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConnection"].ToString()))
            {
                conn.Open();
                DataTable dt = new DataTable();
                string sql = "insert into tbHouse_owner_Info (H_P_phone,H_P_telephone ,H_P_name)values(@H_P_cellphone,@H_P_telephone,@H_P_name) select SCOPE_IDENTITY() as id";
                using (SqlDataAdapter sda = new SqlDataAdapter(sql, conn))
                {
                    parm = sda.SelectCommand.Parameters.Add("@H_P_cellphone", cell);
                    parm = sda.SelectCommand.Parameters.Add("@H_P_telephone", NOphone);
                    parm = sda.SelectCommand.Parameters.Add("@H_P_name", people);
                    sda.Fill(dt);
                     p_id  = dt.Rows[0]["id"].ToString();
}这样的SQL语句执行 用的是 dataadapter 怎么开事务?  语法怎么写? 
写对就送分......在线

Answer 1

/// <summary>  
/// 一般的ADO.net 事务 
/// </summary>  public void ADONetTran1() 
{ 
SqlConnection conn = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=123;"); 
SqlCommand cmd = new SqlCommand(); 
try  { 
cmd.CommandText = "Update Region Set RegionDescription=@UpdateValue where RegionID=@UpdateID"; 
cmd.CommandType = CommandType.Text; 
cmd.Connection = conn; conn.Open(); 
SqlParameter[] paras = new SqlParameter[]{ 
new SqlParameter ("@UpdateID",SqlDbType.Int,32), 
new SqlParameter ("@UpdateValue",SqlDbType .NChar,50)}; 
paras[0].Value = "2"; 
paras[1].Value = "Update Value12"; 
foreach (SqlParameter para in paras) { 
cmd.Parameters.Add(para); } //开始事务  
cmd.Transaction = conn.BeginTransaction();
 cmd.ExecuteNonQuery(); 
cmd.CommandText = "insert into Region values(@InsertID,@InsertValue)"; 
cmd.CommandType = CommandType.Text; 
paras = new SqlParameter[]{ 
new SqlParameter ("@InsertID",SqlDbType.Int ,32), 
new SqlParameter ("@InsertValue",SqlDbType.NChar ,50)}; 
paras[0].Value = "7"; 
paras[1].Value = "Insert Value"; 
cmd.Parameters.Clear();
 foreach (SqlParameter para in paras) {
 cmd.Parameters.Add(para); } 
cmd.ExecuteNonQuery(); //提交事务  
cmd.Transaction.Commit(); 
} catch  { 
//回滚事务  
cmd.Transaction.Rollback(); 
throw; 
} 
finally  { 
conn.Close();
 } 
}

Answer 2

SqlConnection con =new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConnection"].ToString());
        con.Open();
        SqlDataAdapter sda = new SqlDataAdapter();
        DataSet ds = new DataSet();
        SqlCommand cmd = new SqlCommand("select * from TTestInfo where TestYear=@TestYear and GoodCode=@GoodCode", con);
        SqlParameter spr1 = cmd.Parameters.Add("@TestYear", SqlDbType.Int);
        spr1.Value = Convert.ToInt32(year);
        SqlParameter spr2 = cmd.Parameters.Add("@GoodCode", SqlDbType.VarChar, 4);
        spr2.Value = goodcode;
        sda.SelectCommand = cmd;
        sda.Fill(ds, "item");
        con.Close();

Answer 3

dataadapter 怎么开事务?
-----------------------------------
不是很理解,参考这样:
// Start a local transaction.
SqlTransaction myTrans = myConnection.BeginTransaction();// Enlist the command in the current transaction.
SqlCommand myCommand = myConnection.CreateCommand();
myCommand.Transaction = myTrans;try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Both records are written to database.");
}
catch(Exception e)
{
try
{
    myTrans.Rollback();
}
catch (SqlException ex)
{
    if (myTrans.Connection != null)
    {
      Console.WriteLine("An exception of type " + ex.GetType() +
                        " was encountered while attempting to roll back the transaction.");
    }
}

Answer 4

可不可以在sql中直接进行事务处理

Answer 5

不对哦..我用的是 dataadapter 有返回值的  和你这样的不一样的

Answer 6

 以上都是 sqlcommnd里的写法
 但是 在C#里  用 sqldataAdateper 的 怎么 建立连接
 

Answer 7

就一条插入语句有必要用事务吗？
 using (conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConnection"].ToString()))
            {
                conn.Open();
                DataTable dt = new DataTable();
                SqlTransaction myTrans = myConnection.BeginTransaction();
                string sql = "begin tran insert into tbHouse_owner_Info (H_P_phone,H_P_telephone ,H_P_name)values(@H_P_cellphone,@H_P_telephone,@H_P_name) select SCOPE_IDENTITY() as id if   @@error<>0 begin  Rollback  tran return end  commit  tran ";
                using (SqlDataAdapter sda = new SqlDataAdapter(sql, conn))
                {
                    parm = sda.SelectCommand.Parameters.Add("@H_P_cellphone", cell);
                    parm = sda.SelectCommand.Parameters.Add("@H_P_telephone", NOphone);
                    parm = sda.SelectCommand.Parameters.Add("@H_P_name", people);
                    sda.Fill(dt);
                    p_id = dt.Rows[0]["id"].ToString();
                }
            }

Answer 8

private static void ExecuteSqlTransaction(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();        SqlCommand command = connection.CreateCommand();
        SqlTransaction transaction;        // Start a local transaction.
        transaction = connection.BeginTransaction("SampleTransaction");        // Must assign both transaction object and connection
        // to Command object for a pending local transaction
        command.Connection = connection;
        command.Transaction = transaction;        try
        {
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
            command.ExecuteNonQuery();
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
            command.ExecuteNonQuery();            // Attempt to commit the transaction.
            transaction.Commit();
            Console.WriteLine("Both records are written to database.");
        }
        catch (Exception ex)
        {
            Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
            Console.WriteLine("  Message: {0}", ex.Message);            // Attempt to roll back the transaction.
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                // This catch block will handle any errors that may have occurred
                // on the server that would cause the rollback to fail, such as
                // a closed connection.
                Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                Console.WriteLine("  Message: {0}", ex2.Message);
            }
        }
    }
}

Answer 9

 using (conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConnection"].ToString()))
            {
                conn.Open();
                DataTable dt = new DataTable();
                SqlTransaction myTrans = myConnection.BeginTransaction();
                string sql = "begin tran insert into tbHouse_owner_Info (H_P_phone,H_P_telephone ,H_P_name)values(@H_P_cellphone,@H_P_telephone,@H_P_name) select SCOPE_IDENTITY() as id if   @@error<>0 begin  Rollback  tran return end  commit  tran ";
                using (SqlDataAdapter sda = new SqlDataAdapter(sql, conn))
                {
                    parm = sda.SelectCommand.Parameters.Add("@H_P_cellphone", cell);
                    parm = sda.SelectCommand.Parameters.Add("@H_P_telephone", NOphone);
                    parm = sda.SelectCommand.Parameters.Add("@H_P_name", people);
                    sda.SelectCommand.Transaction = myTrans;
                    try
                    {
                        sda.Fill(dt);
                        p_id = dt.Rows[0]["id"].ToString();
                        myTrans.Commit();
                    }
                    catch (Exception ex)
                    {
                        myTrans.Rollback();
                    }
                }
            }

Answer 10

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConnection"].ToString()))
        {
            conn.Open();
            SqlTransaction tras = null;
            tras = conn.BeginTransaction();//开始事务
            DataTable dt = new DataTable();
            string sql = "insert into tbHouse_owner_Info (H_P_phone,H_P_telephone ,H_P_name)values(@H_P_cellphone,@H_P_telephone,@H_P_name) select SCOPE_IDENTITY() as id";
            using (SqlDataAdapter sda = new SqlDataAdapter(sql, conn))
            {
                parm = sda.SelectCommand.Parameters.Add("@H_P_cellphone", cell);
                parm = sda.SelectCommand.Parameters.Add("@H_P_telephone", NOphone);
                parm = sda.SelectCommand.Parameters.Add("@H_P_name", people);
                sda.Fill(dt);
                tras.Commit();//提交事务
                p_id = dt.Rows[0]["id"].ToString();
            }
        }
应该是这样吧，楼主可以试一下。