对存储的操作

来源：互联网发布：linux init 6 编辑：程序博客网时间：2024/05/04 07:57

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Net;
using System.Data;
using System.Configuration;

namespace NewsLib.DAO
{
    public class SqlHelper
    {
        //自动获取主机名
        string hostName = "192.168.0.188";//Dns.GetHostName();
        //创建sqlconnection对象
        SqlConnection conn = null;
        //查找连接字符串
        public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["newsConnString"].ConnectionString;

        /// <summary>
        /// 创建连接并打开连接
        /// </summary>
        /// <returns></returns>
        public SqlConnection InnitConnect()
        {
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString; //"Persist Security Info=False;User ID=sa;Initial Catalog=NewsInfo;Data Source=" + hostName;
                conn.Open();
                return conn;
            }
            catch (Exception e)
            {
                Console.Write(e.Message);
            }
            return null;
        }
        /// <summary>
        /// 执行查询
        /// 操作数据的形式是以存储过程的形式
        /// </summary>
        /// <param name="storeProcName">存储过程名</param>
        /// <param name="pars">参数集合</param>
        /// <returns>返回有个SqlDataSet对象保存所查询的信息</returns>
        public DataSet ExecuteDataset(string storeProcName, SqlParameter[] pars)
        {
            DataSet ds = new DataSet();
            try
            {
                using (SqlConnection conn = InnitConnect())
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        //连接对象
                        cmd.Connection = conn;
                        //指定存储过程名
                        cmd.CommandText = storeProcName;
                        //以数组的形式指定参数列表
                        cmd.Parameters.AddRange(pars);
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        //填充dataset对象
                        sda.Fill(ds);
                        int a = ds.Tables[0].Rows.Count;
                        return ds;
                    }
                }
            }
            catch(Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return null;
        }
        /// <summary>
        /// 执行分页查询
        /// 操作数据的形式是以存储过程的形式
        /// </summary>
        /// <param name="storeProcName">存储过程名</param>
        /// <param name="pars">参数集合</param>
        /// <param name="pages" >返回页数和记录数</parm>
        /// <returns>返回有个SqlDataSet对象保存所查询的信息</returns>
        public DataSet ExecuteDatasetByPageIndex(string storeProcName, SqlParameter[] pars,ref string[] pages)
        {
            DataSet ds = new DataSet();
            pages = new string[2];
            try
            {
                using (SqlConnection conn = InnitConnect())
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        //连接对象
                        cmd.Connection = conn;
                        //指定存储过程名
                        cmd.CommandText = storeProcName;
                        //以数组的形式指定参数列表
                        cmd.Parameters.AddRange(pars);
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        //填充dataset对象
                        sda.Fill(ds);
                        pages[0] = cmd.Parameters["@allRecord"].Value.ToString ();
                        pages[1] = cmd.Parameters["@pageCount"].Value.ToString ();
                        return ds;
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return null;
        }
        /// <summary>
        /// 执行查询
        /// 操作数据的形式是以存储过程的形式
        /// </summary>
        /// <param name="storeProcName">存储过程名</param>
        /// <returns>返回有个SqlDataSet对象保存所查询的信息</returns>
        public DataSet ExecuteDataset(string storeProcName)
        {
            DataSet ds = new DataSet();
            try
            {
                using (SqlConnection conn = InnitConnect())
                {
                    //以commandText和连接实例化一个SqlDataAdapter
                    SqlDataAdapter sda = new SqlDataAdapter(storeProcName,conn);
                    //填充dataset对象
                    sda.Fill(ds);

                    return ds;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return null;
        }
        /// <summary>
        /// 更新或删除增加
        /// 以存储过程的形式添加擅长修改数据
        /// 如果影响的行数大于0 说明有行被影响了返回true 否则false
        /// </summary>
        /// <param name="storeProcName">存储过程名</param>
        /// <param name="pars">参数列表</param>
        /// <returns></returns>
        public bool ExecuteNonQuery(string storeProcName, SqlParameter[] pars)
        {
            try
            {
                using (SqlConnection conn = InnitConnect())
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        //连接对象
                        cmd.Connection = conn;
                        //指定存储过程名
                        cmd.CommandText = storeProcName;
                        //循环给参数赋值
                        cmd.Parameters.AddRange(pars);
                        //返回影响的行数
                        int m = cmd.ExecuteNonQuery();
                        if (m > 0)
                            return true;
                        else
                            return false;
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return false ;
        }

        /// <summary>
        /// 根据存储过程查找存储过程的参数
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <returns></returns>
        public SqlParameter[] GetParameterByProcName(string procName)
        {
            //参数数组
            SqlParameter[] pms = null;
            try
            {
                using (conn = new SqlConnection())
                {
                    InnitConnect();
                    //命令对象
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        //存储过程名
                        cmd.CommandText = procName;
                        cmd.Connection = conn;
                        //清楚参数
                        cmd.Parameters.Clear();
                        //执行cmd 对象返回参数数组
                        SqlCommandBuilder.DeriveParameters(cmd);

                        pms = new SqlParameter[cmd.Parameters.Count - 1];
                        for (int i = 1; i < cmd.Parameters.Count; i++)
                        {
                            // 在此要clone参数列表不能直接给因为唯一性应用接口CLONE
                            //注意cmd返回的第一个参数对操作来说是无效的从第二个开始
                            pms[i - 1] = (SqlParameter)((ICloneable)cmd.Parameters[i]).Clone();
                        }
                    }
                }
                return pms;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return null;
        }
    }
}