C#的SQL数据访问

 C#的SQL数据访问

方法之一

 

 

#region 增删改的公用方法

        /// <summary>
        /// 增、删、改的方法

        /// </summary>
        /// <param name="sql">查询语句或者存储过程</param>
        /// <param name="isProc">是否启用存储过程</param>
        /// <param name="pList">存储过程参数列表</param>
        /// <returns>返回影响的行数</returns>
        public static int ManageDataByProc(string sql, bool isProc, params SqlParameter[] pList)
        {
            SqlConnection con = new SqlConnection(数据库连接串);
            SqlCommand cmd = new SqlCommand(sql, con);

            foreach (SqlParameter p in pList)
            {
                cmd.Parameters.Add(p);
            }
            if (isProc)
                cmd.CommandType = CommandType.StoredProcedure;
            SqlTransaction trans = null;
            int n = 0;
            try
            {
                con.Open();
                trans = con.BeginTransaction();
                cmd.Transaction = trans;
                n = cmd.ExecuteNonQuery();
                if (n > 0)
                {
                    trans.Commit();
                    con.Close();
                    return n;
                }
                else
                {
                    trans.Rollback();
                    con.Close();
                    return n;
                }
            }
            catch (Exception ex)
            {
                trans.Rollback();
                con.Close();
                return n;
                throw new Exception(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }
        }
        /// <summary>
        /// 根据SQL语句或者存储过程,获得数据表
        /// </summary>
        /// <param name="sql">存储过程名或者SQL语句</param>
        /// <param name="tableName">表名</param>
        /// <param name="isProc">是否启用存储过程</param>
        /// <param name="pList">参数列表</param>
        /// <returns>数据表</returns>
        public static DataTable GetDataByProc(string sql, string tableName, bool isProc, params SqlParameter[] pList)
        {
            using (SqlConnection con = new SqlConnection(数据库连接串))
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                //如果有参数，加入参数
                if (isProc)
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter p in pList)
                    da.SelectCommand.Parameters.Add(p);
                using (DataSet ds = new DataSet())
                {
                    if (tableName != null)
                        da.Fill(ds, tableName);
                    else
                        da.Fill(ds);
                    return ds.Tables[0];
                }
            }
        }
        #endregion