C#中对SQL数据库中存储过程及表的操作

 

    public class SqlConnectModule:IDisposable
    {
        private static string connStr = "SERVER=CHINA-DLX2011;DataBase=iSOSDB;user id=sa;password=sa";
        private SqlConnection conn = new SqlConnection(connStr);

        /// <summary>
        /// 执行存储过程，返回DataSet
        /// </summary>
        /// <param name="produceName"></param>
        /// <param name="prams"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string produceName, SqlParameter[] prams)
        {
            DataSet dataset = new DataSet();
            SqlCommand sqlCommand = new SqlCommand(produceName, conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            if (prams != null)
            {
                foreach (SqlParameter parm in prams)
                {
                    sqlCommand.Parameters.Add(parm);
                }
            }
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dataset);
            sqlCommand.Parameters.Clear();
            return dataset;
        }

        /// <summary>
        /// 执行存储过程，返回byte[]类型图片信息
        /// </summary>
        /// <param name="produceName"></param>
        /// <param name="prams"></param>
        /// <returns></returns>
        public byte[] ExecuteDataReader(string produceName, SqlParameter[] prams)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            byte[] imageDate = null;
            SqlCommand sqlCommand = new SqlCommand(produceName, conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            if (prams != null)
            {
                foreach (SqlParameter parm in prams)
                {
                    sqlCommand.Parameters.Add(parm);
                }
            }
            SqlDataReader sr = sqlCommand.ExecuteReader();
            while (sr.Read())
            {
                imageDate = (byte[])sr[0];
            }
            sr.Close();
            sqlCommand.Parameters.Clear();
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
            return imageDate;
        }

        /// <summary>
        /// 执行存储过程，向表中添加记录
        /// </summary>
        /// <param name="produceName"></param>
        /// <param name="prams"></param>
        public void ExecuteDataTable(string produceName, SqlParameter[] prams)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlCommand sqlCommand = new SqlCommand(produceName, conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            if (prams != null)
            {
                foreach (SqlParameter parm in prams)
                {
                    sqlCommand.Parameters.Add(parm);
                }
            }
            try
            {
                sqlCommand.ExecuteNonQuery();
            }
            catch
            {
                conn.Close();
            }
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
            sqlCommand.Parameters.Clear();
        }

        /// <summary>
        /// 执行存储过程，得到表中某列字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        public ArrayList GetTableField(string tableName, string colName)
        {
            ArrayList colFieldArraylist = new ArrayList();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = conn;
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            try
            {
                sqlCommand.CommandText = "select " + colName + " from " + tableName;
                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    colFieldArraylist.Add(sqlDataReader[0].ToString());
                }
                sqlDataReader.Close();
                return colFieldArraylist;
            }
            catch
            {
                return colFieldArraylist;
            }
            conn.Close();
        }

        /// <summary>
        /// 删除表中全部记录
        /// </summary>
        /// <param name="tableName"></param>
        public void DeleteTableRecord(string tableName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Connection = conn;
            conn.Open();
            try
            {
                sqlCmd.CommandText = "delete from " + tableName;
                sqlCmd.ExecuteNonQuery();
            }
            catch
            {
                return;
            }
            conn.Close();
        }

        /// <summary>
        /// 删除表中指定行记录
        /// </summary>
        /// <param name="tableName"></param>
        public void DeleteRowRecord(string rowName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Connection = conn;
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            try
            {
                sqlCmd.CommandText = "delete from iSosImageInfos where ImageName='" + rowName + "'";
                sqlCmd.ExecuteNonQuery();
            }
            catch
            {
                return;
            }
            conn.Close();
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        /// <summary>
        /// 释放连接
        /// </summary>
        /// <param name="disposing"></param>
        protected virtual void Dispose(bool disposing)
        {
            if (disposing != true)
            {
                return;
            }
            if (conn != null )
            {
                conn.Dispose();
                conn = null;
            }
        }
    }