c# 操作Sql Server 数据库

话不多说,直接上代码

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CommonUtils
{
    class SqlServerConnect
    {
		//连接字符串
		//Data Source=127.0.0.1;
		//Initial Catalog=Object; 数据库名
		//User=sa;  用户名
		//PassWord=123456   密码
        private static readonly string constr = "Data Source=192.168.66.3;Initial Catalog=Object;User=sa;PassWord=123456";

        /// <summary>
        /// 增、删、改的方法[ExecuteNonQuery] 返回所影响的行数,执行其他
        /// 执行成功返回受影响的行数  执行失败返回-1
        /// </summary>
        /// <param name="sql">Sql命令</param>
        /// <param name="pms">参数</param>
        /// <returns>返回所影响的行数</returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)
        {
            int i = -1;
            try
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand(sql, con))
                    {
                        //存储过程或者Sql语句
                        if (pms != null)
                        {
                            cmd.Parameters.AddRange(pms);
                        }

                        con.Open();
                        i = cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }

            return i;
        }

        /// <summary>
        /// 执行查询、查询多少条数据;返回第一行,第一列方法[ExecuteScalar] 返回-1执行失败 
        /// 多用于聚合函数如;clount(*)   sum()
        /// </summary>
        /// <param name="sql">Sql命令</param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] pms)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand(sql, con))
                    {
                        if (pms != null)
                        {
                            cmd.Parameters.AddRange(pms);
                        }

                        con.Open();
                        return cmd.ExecuteScalar();
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 执行查询,返回多行、多列的方法ExecuteReader()
        /// </summary>
        /// <param name="sql">Sql命令</param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand(sql, con))
                    {
                        if (pms != null)
                        {
                            cmd.Parameters.AddRange(pms);
                        }
                        con.Open();
                        return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }

        }

        /// <summary>
        /// 执行返回多个查询时使用,返回DataTable类型
        /// </summary>
        /// <param name="sql">Sql命令</param>
        /// <param name="pms">参数</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql,params SqlParameter[] pms)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter(sql, constr))
                {
                    if (pms != null)
                    {
                        adapter.SelectCommand.Parameters.AddRange(pms);
                    }

                    adapter.Fill(dt);
                    return dt;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 执行返回多个查询时使用,返回List<T>类型(待试验)
        /// </summary>
        /// <typeparam name="T">对应数据库的模型类</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="pms">参数</param>
        /// <returns>返回List<T>类型</returns>
        public List<T> QueryList<T>(string sql, params SqlParameter[] pms)
        {
            DataTable dt = new DataTable();
            List<T> list = new List<T>();
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, constr))
            {
                if (pms != null)
                {
                    adapter.SelectCommand.Parameters.AddRange(pms);
                }

                adapter.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    T _t = Activator.CreateInstance<T>();

                    PropertyInfo[] propertyInfo = _t.GetType().GetProperties();

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        foreach (PropertyInfo info in propertyInfo)
                        {
                            if (dt.Columns[j].ColumnName.ToUpper().Equals(info.Name.ToUpper()))
                            {
                                if (dt.Rows[i][j] != DBNull.Value)
                                {
                                    info.SetValue(_t, dt.Rows[i][j], null);
                                }
                                else
                                {
                                    info.SetValue(_t, null, null);
                                }

                                break;
                            }
                        }
                    }
                    list.Add(_t);
                }
            }
            return list;

        }
    }
}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值