C# Ado.NET连接Sql-server

 

一个Sql-server数据库连接，只适合初学者哦

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

namespace DataAccess
{
    /// <summary>
    /// 操作数据库公共类
    /// </summary>
    class SqlDataAccess : IDisposable
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
       
        private readonly string SqlConnectionString = "连接字符串;";

        private SqlConnection sqlConnection;

        public SqlDataAccess()
        {
            sqlConnection = new SqlConnection(SqlConnectionString);
        }
        /// <summary>
        /// 执行SQL语句，并返回数据库受影响的行数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public int ExcuteNonQuery(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, sqlConnection);
            if (sqlConnection.State == ConnectionState.Closed)
            {
                sqlConnection.Open();
            }
            int result = cmd.ExecuteNonQuery();
            sqlConnection.Close();
            return result;
        }

　　　　//如果更新的数据有特殊字符的用上面那个ExcuteNonQuery()就不行了，需要用到SqlCommand 的一个参数SqlParameter

　　　　public int ExcuteNonQuery(string sql,SqlParameter[] parameters)
        　　{

            SqlCommand cmd = new SqlCommand(sql, sqlConnection);
            cmd.Parameters.AddRange(parameters);
            if (sqlConnection.State == ConnectionState.Closed)
            {
                sqlConnection.Open();
            }
            int count = cmd.ExecuteNonQuery();
            sqlConnection.Close();
            return count;
        　　}

        /// <summary>
        /// 返回首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, sqlConnection);
            if (sqlConnection.State == ConnectionState.Closed)
            {
                sqlConnection.Open();
            }
            object result = cmd.ExecuteScalar();
            sqlConnection.Close();
            return result;
        }
        /// <summary>
        /// 获取一个DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(sql, sqlConnection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }

     }
}

示例：用上面的数据库连接类，新增一个商品，包括商品名称和单价

string insertSql = @"INSERT INTO PRODUCTINFO
                                    VALUES
                                    (
                                    @PRODUCTNAME,
                                    @PRODUCTPRICE,

　　　　　　　　　　　　　);

SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@PRODUCTNAME",商品名称){SqlDbType=SqlDbType.NVarChar},
                new SqlParameter("@PRODUCTPRICE",商品单价){SqlDbType=SqlDbType.Money}

　　　　}

DataAccess dataAccess = new DataAccess();
            int result = dataAccess.ExcuteNonQuery(insertSql, parameters);
            if (result > 0)
            {
                MessageBox.Show("新增成功");
            }
            else
            {
                MessageBox.Show("新增失败");
            }