C#数据库学习笔记（一）

1.连接数据库Connection

定义连接字符串；

Data Source= .\\SQLEXPRESS(服务器名);Initial Catalog=PAYMENTS（数据库名）;Integrated Security=true（集成安全连接）;

连接本地服务器
SqlConnection conn = new SqlConnection("server=(local);Initial Catalog =BookStore; User ID=Administrator（用户名）;Password = ms2218（密码） ");
使用IP连接
SqlConnection conn = new SqlConnection("addr=192.168.0.128;Initial Catalog =BookStore; User ID=Administrator;Password = ms2218 ");
连接数据库文件
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename = D:\\BookStore.mdfDatabase = BookStore;Integrated Security = SSPI";

得到连接字符串

1）连接字符串生成器SqlConnectionStringBuilder

            SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
            scsb.DataSource = ".\\SQLEXPRESS";  一般用localhost
            scsb.InitialCatalog = "PAYMENTS";
            scsb.IntegratedSecurity = true;
            string strcon3 = scsb.ConnectionString;
            Console.WriteLine(strcon3);
            Console.ReadLine();

2）VS中直接连接，得到连接的高级属性，复制即可

连接字符串的储存和加密

储存在管理中，ConfigurationManager类

创建配置文件（新建项的应用程序配置文件）

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="ConPAYMENTS" connectionString="Data Source=.\\SQLEXPRESS;Initial Catalog=PAYMENTS;Integrated Security=true"/>     
  </connectionStrings>
</configuration>

添加System.Configuration命名空间

            string strCon5 = ConfigurationManager.ConnectionStrings["ConPAYMENTS"].ConnectionString;
            Console.WriteLine(strCon5);
            Console.ReadLine();

加密配置文件（配置文件包括用户名和密码，可以把下述代码提取为方法）

            Configuration config= ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            config.ConnectionStrings.SectionInformation.ProtectSection(null);
            config.Save(ConfigurationSaveMode.Full);

创建Connection对象；

SqlConnection con = new SqlConnection(strcon)；

打开数据库的连接；

con.Open();

关闭数据连接。

con.Close();

也可以使用using语句控制数据库的打开和关闭

SqlConnection con = null;
SqlCommand cmd = null;

 SqlDataReader sdr = null;
            try
            {
               using (con = new SqlConnection(strcon))
                {
                    cmd = con.CreateCommand();
                    cmd.CommandText = "select NAME from BILLS";
                    con.Open();
                    sdr = cmd.ExecuteReader();
                    while (sdr.Read())
                    {
                        Console.WriteLine(sdr[0]);//循环输出                   
                    }
                    Console.WriteLine(con.State);//当前状态，输出为open
                    Console.ReadLine();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

Console.WriteLine(con.State);//当前状态，输出为close

2.连接池的使用

连接池是一种在打开数据储存区的连接时提高应用程序性能的机制。不会反复打开关闭，不用的时候处于闲置状态。

3.创建SqlCommand对象

使用不带参构造函数

SqlCommand cmd=new SqlCommand();

cmd.Connection=SqlConnection对象

cmd.CommandText=sql语句

使用带参函数

SqlCommand cmd=new SqlCommand(sql语句;SqlConnection对象);zhixing

调用SqlConnection对象的CreateCommand方法

SqlConnection con = new SqlConnection(strcon)；

  cmd = con.CreateCommand();
  cmd.CommandText = "select NAME from BILLS";
4.使用SqlCommand执行查询

执行返回的查询方法，ExecuteReader();
获取单一值，ExecuteScalar

                    cmd = con.CreateCommand();
                    string sql="select count(*) from BILLS";
                    cmd.CommandText = sql;
                    con.Open();
                    int count=Int32.Parse(cmd.ExecuteScalar().ToString());
                    Console.WriteLine("总行数：{0}",count);

执行不返回结果集的查询，ExecuteNoneQuery，返回结果为几行受影响。

数据操作语言查询（Insert,update,delete）

数据定义查询（create table）

执行批量操作查询(StatementCompleted)

操作多条SQL语句，如果中间一条语句出错，则全部不执行。

返回每条语句影响的行数：

string sql="sql语句1"+"sql语句2";

con.Open();

cmd.CommandText=sql;

cmd.StatementCompleted += new System.Data.StatementCompletedEventHandler(cmd_StatementCompleted);

//需添加命名空间System.Data

int res=cmd.ExecuteNoneQuery;

Console.Writeline(res);

并构建函数

static void cmd_StatementCompleted(Object sender,StatementCompletedEventHandler e)
        {
            Console.WriteLine(e.RecordCount);
        }

异步查询大结果集

string strcon = "Data Source=.\\SQLEXPRESS;Initial Catalog=PAYMENTS;Integrated Security=true;Asynchronous Processing=true";//连接
            using (SqlConnection con = new SqlConnection(strcon))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "waitfor delay '00:00:10';select * from BILLS";
                    con.Open();
                    IAsyncResult iar = cmd.BeginExecuteReader();
                    //其他操作
                    SqlDataReader sdr = cmd.EndExecuteReader(iar);
                    while (sdr.Read())
                    {
                        Console.WriteLine(sdr[0]);
                    }
                    Console.ReadLine();