C#中数据库事务、存储过程基本用法

SQL 事务

 public bool UpdateQsRegisterSql(List<string> ids, int newQueueId, string newQueueName)
        {
            using (SqlConnection con = new SqlConnection(DBHelper.Instance.ConStr))
            {
                SqlTransaction tran = null;
                try
                {
                    con.Open();
                    SqlCommand cmd = con.CreateCommand();
                    tran = con.BeginTransaction(IsolationLevel.ReadCommitted);
                    cmd.Transaction = tran;

                    foreach (string item in ids)
                    {
                        cmd.CommandText = @"update qs_register set queueid='" + newQueueId
                            + "',queuename='" + newQueueName + "' where id='" + item + "'";
                        cmd.ExecuteNonQuery();
                    }

                    tran.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    return false;
                }
            }
        }

Oracle事务

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

public bool UpdateQsRegisterOrcl(List<string> ids, int newQueueId, string newQueueName)       {           using (OleDbConnection con = new OleDbConnection(DBHelper.Instance.ConStr))           {               OleDbTransaction tran = null;               try               {                   con.Open();                   OleDbCommand cmd = con.CreateCommand();                   tran = con.BeginTransaction(IsolationLevel.ReadCommitted);                   cmd.Transaction = tran;                     foreach (string item in ids)                   {                       cmd.CommandText = @"update qs_register set queueid='" + newQueueId                           + "',queuename='" + newQueueName + "' where id='" + item + "'";                       cmd.ExecuteNonQuery();                   }                     tran.Commit();                   return true;               }               catch (Exception ex)               {                   tran.Rollback();                   return false;               }           }       }

Sql存储过程

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

/// <summary>    /// 直接登记    /// </summary>    /// <param name="hisExamno">申请单号</param>    /// <param name="enrolDoctor">登记医生</param>    /// <param name="engname">患者英文名称</param>    /// <param name="queueName">队列名称</param>    /// <returns></returns>    public bool DirectRegisterSql(string hisExamno, string enrolDoctor, string engname, string queueName)    {        using (SqlConnection conn = new SqlConnection(DBHelper.Instance.ConnectionStr))        {            conn.Open();            SqlCommand cmd = conn.CreateCommand();            try            {                //插入号源属性                cmd.CommandType = CommandType.StoredProcedure;                cmd.CommandText = "PROC_HISEXAMTOENROL";                cmd.Parameters.Add("@p_HISEXAMNO", SqlDbType.VarChar).Value = hisExamno;                cmd.Parameters.Add("@p_ENROLDOCTOR", SqlDbType.VarChar).Value = enrolDoctor;                cmd.Parameters.Add("@p_ENGNAME", SqlDbType.VarChar).Value = engname;                cmd.Parameters.Add("@p_AppKind", OleDbType.VarChar).Value = PubVariable.Instance.CurrentDept;                cmd.Parameters.Add("@p_QueueName", OleDbType.VarChar).Value = queueName;                cmd.Parameters.Add("@p_result", SqlDbType.Int).Value = -1;                  if (cmd.ExecuteNonQuery() > 0) return true;            }            catch { return false; }            finally            {                if (conn.State != ConnectionState.Closed)                {                    conn.Close();                }            }        }        return false;    }

Oracle存储过程

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

/// <summary>       /// 直接登记       /// </summary>       /// <param name="hisExamno">申请单号</param>       /// <param name="enrolDoctor">登记医生</param>       /// <param name="engname">患者英文名称</param>       /// <param name="queueName">队列名称</param>       /// <returns></returns>       public bool DirectRegisterOrcl(string hisExamno, string enrolDoctor, string engname, string queueName)       {           using (OleDbConnection conn = new OleDbConnection(DBHelper.Instance.ConnectionStr))           {               conn.Open();               OleDbCommand cmd = conn.CreateCommand();               try               {                   //插入号源属性                   cmd.CommandType = CommandType.StoredProcedure;                   cmd.CommandText = "PROC_HISEXAMTOENROL";                   cmd.Parameters.Add("@p_HISEXAMNO", OleDbType.VarChar).Value = hisExamno;                   cmd.Parameters.Add("@p_ENROLDOCTOR", OleDbType.VarChar).Value = enrolDoctor;                   cmd.Parameters.Add("@p_ENGNAME", OleDbType.VarChar).Value = engname;                   cmd.Parameters.Add("@p_AppKind", OleDbType.VarChar).Value = PubVariable.Instance.CurrentDept;                   cmd.Parameters.Add("@p_QueueName", OleDbType.VarChar).Value = queueName;                   cmd.Parameters.Add("@p_result", OleDbType.Integer).Value = -1;                     if (cmd.ExecuteNonQuery() > 0) return true;               }               catch { return false; }               finally               {                   if (conn.State != ConnectionState.Closed)                   {                       conn.Close();                   }               }           }           return false;       }