C#操作数据库

浏览数：23 / 时间：2015年06月12日

1 : App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
      <add name="ConnLink" connectionString="server=‘localhost‘;database=‘ReportServer‘;UID= ‘sa‘;PassWord=‘123‘"></add>
    </connectionStrings>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
</configuration>

improtant ：

    <connectionStrings>
      <add name="ConnLink" connectionString="server=‘localhost‘;database=‘ReportServer‘;UID= ‘sa‘;PassWord=‘123‘"></add>
    </connectionStrings>

add Quote

code ：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace SQLHandel.SQL
{
    /// <summary>
    /// 注意此类
    /// </summary>
    public class SqlConnectionDemo
    {
        private readonly string CONN_LINK = ConfigurationManager.ConnectionStrings["ConnLink"].ToString();
        public SqlConnectionDemo()
        { 
            //test
            Console.WriteLine("我得到的链接数据库的连接字符串 - > " + this.CONN_LINK);
        }
        /// <summary>
        /// 链接数据库
        /// </summary>
        /// <returns>是否连接成功</returns>
        public bool ConnDB()
        {
            SqlConnection conn = new SqlConnection(this.CONN_LINK);
            bool connOK = false;
            try
            {
                conn.Open();
                connOK = true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                conn.Close();
            }
            return connOK;
        }
        /// <summary>
        /// 使用SqlDataReader 来获取数据
        /// </summary>
        public void getDataWithDataReader()
        {
            //注意 , 这一使用using , 确保conn对象的释放
            using (SqlConnection conn = new SqlConnection(this.CONN_LINK))
            {
                string sql = "select * from Users where UserType = @UserType";
                SqlParameter spt1 = new SqlParameter("UserType", SqlDbType.Int); // 注意 : SqlDbType 引用 using System.Data;
                spt1.Value = 1;//占位赋值
                SqlCommand scd = new SqlCommand();
                scd.Connection = conn;
                scd.CommandText = sql;
                scd.Parameters.Add(spt1);//加入到Commend 中
                // 注意打开数据库连接
                conn.Open();
                SqlDataReader dr = scd.ExecuteReader(); // 注 : SqlDataReader 没有构造函数
                while (dr.Read())//Read() 相当于一个指针 , 每读一次向下移动以为  .
                {
                    Console.WriteLine("我得到的数据 DataReader-------------------------");
                    Console.WriteLine("UserId : " + dr["UserId"].ToString());
                    Console.WriteLine("Sid : " + dr["Sid"].ToString());
                    Console.WriteLine("UserType : " + dr["UserType"].ToString());
                    Console.WriteLine("AuthType : " + dr["AuthType"].ToString());
                    Console.WriteLine("UserName : " + dr["UserName"].ToString());
                }
                dr.Dispose();
                scd.Dispose();
                conn.Close();
            }
        }
        /// <summary>
        /// 私用DataSet获得数据集(本人建议使用)
        /// </summary>
        public void getDataWithDataSet()
        {
            using (SqlConnection conn = new SqlConnection(this.CONN_LINK))
            {
                string sql = "select * from Users where UserType = @UserType ";
                SqlDataAdapter sda = new SqlDataAdapter();
                DataSet ds = new DataSet("Kayer"); //  虚拟库名
                SqlCommand scd = new SqlCommand();
                scd.CommandText = sql;
                scd.Connection = conn;
                SqlParameter spt1 = new SqlParameter("UserType", SqlDbType.Int);
                spt1.Value = 1;
                scd.Parameters.Add(spt1);
                sda.SelectCommand = scd;
                sda.Fill( ds, "users"); // 虚拟表名
                // 对数据的解析
                //第一种遍历的方式
                //foreach (DataRow dr in ds.Tables["users"].Rows)
                //{
                //    //遍历列
                //    Console.WriteLine("我得到的数据 数据 DataSet ----------------------------------");
                //    foreach (DataColumn dc in ds.Tables["users"].Columns)
                //    {
                //        Console.WriteLine(" 列 : {0} , 数据 : {1} ", dc.ColumnName, dr[dc.ColumnName]);
                //    }
                //}
                //第二种遍历的方式
                for (int i = 0; i < ds.Tables["users"].Rows.Count; i += 1)
                {
                    Console.WriteLine("我得到的数据 数据 DataSet ----------------------------------");
                    for (int j = 0; j < ds.Tables["users"].Columns.Count; j += 1)
                    {
                        Console.WriteLine(" 列 : {0} , 数据 : {1} ", ds.Tables["users"].Columns[j].ColumnName, ds.Tables["users"].Rows[i][j]);
                    }
                }
                ds.Dispose();
                sda.Dispose();
            }
        }
        /// <summary>
        /// 运行无参存储过程
        /// </summary>
        public void ProNoPramHandler()
        {
            using (SqlConnection conn = new SqlConnection(this.CONN_LINK))
            {
                SqlCommand scd = new SqlCommand("ProNameNoParams", conn);
                scd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                scd.ExecuteNonQuery();
                scd.Dispose();
                conn.Dispose();
            }
        }
        /// <summary>
        /// 运行有参存储过程
        /// </summary>
        public void ProHaspramHander()
        {
            using (SqlConnection conn = new SqlConnection(this.CONN_LINK))
            {
                SqlCommand scd = new SqlCommand();
                scd.Connection = conn;
                scd.CommandText = "ProNameNoParams";//存储过程的名称
                scd.CommandType = CommandType.StoredProcedure;
                SqlParameter spt = new SqlParameter("@ParameterName", SqlDbType.Int);//存储过程的参数 
                spt.Direction = ParameterDirection.Input; // 输入参数
                spt.Value = 1;//赋值
                scd.Parameters.Add(spt);
                conn.Open();
                scd.ExecuteNonQuery();
                scd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        /// <summary>
        /// 运行有返回值存储过程
        /// </summary>
        public void ProHasReturnHandler()
        {
            using (SqlConnection conn = new SqlConnection(this.CONN_LINK))
            {
                SqlCommand scd = new SqlCommand();
                scd.Connection = conn;
                scd.CommandText = "ProHasReturn";
                scd.CommandType = CommandType.StoredProcedure;
                SqlParameter spt = new SqlParameter("@id", SqlDbType.Int);
                spt.Direction = ParameterDirection.Output;//存储过程返回参数设置
                scd.Parameters.Add(spt);
                conn.Open();
                scd.ExecuteNonQuery();
                int returnfromPro = Convert.ToInt32(scd.Parameters["@id"].Value);//得到存储过程返回参数
                scd.Dispose();
                conn.Close();
            }
        }
    }
}