C#存储过程加事务

来源：互联网发布：oa工作流数据库设计编辑：程序博客网时间：2024/05/10 03:16

public DataSet GettPerformance(string AgentID, string StartDate, string EndDate)
{

            try
            {
                Regex regex = new Regex(@"^\d+W");
                DataSet datas = null;
                int ID = 0;

                if (AgentID != "" && AgentID != null)
                {
                    if (!regex.IsMatch(AgentID))
                    {
                        ID = Convert.ToInt32(AgentID);
                    }
                }
                else
                {

                    ID = 0;
                }
                SqlParameter[] param = new SqlParameter[]{new SqlParameter("@StartDate",StartDate),
                new SqlParameter("@EndDate",EndDate),
                new SqlParameter("@AgentID",ID)
                };

                // string strsql = "EXEC p_GetACDReport '" + StartDate + "','" + EndDate + "'," + ID;
                datas = GetDataSet("p_GetACDReport", param);
                return datas;
            }
            catch
            {
                return null;
            }
        }

        public DataSet GetDataSet(string proName, params SqlParameter[] param)
        {
            DataSet ds = null;

SqlTransaction st = null;

            try
            {
                string connString = ConfigurationManager.ConnectionStrings["WISEDEMO"].ConnectionString;
                SqlConnection SqlConn = new SqlConnection(connString);

                if (SqlConn == null)
                {
                    SqlConn.Open();
                }
                if (SqlConn.State == ConnectionState.Closed)
                {
                    SqlConn.Open();
                }
                if (SqlConn.State == ConnectionState.Broken)
                {
                    SqlConn.Open();
                }

st = SqlConn.BeginTransaction();

ds = new DataSet();

                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = SqlConn;
                cmd.CommandText = proName;

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }

cmd.Transaction = st;

                if (st != null)
                {
                   cmd.ExecuteNonQuery();
                   st.Commit();
                }
                adapter.Fill(ds);

                return ds;
            }
            catch
            {
                st.Rollback();
                return null;

}
}