ADO.net

一、访问数据库的方式

导入命名空间using System.Data.SqlClient;
1、SqlConnection连接数据库；
2、SqlCommand执行Sql语句‘
3、SqlDataReader 执行完毕后将结果一条一条返回；

二、连接字符串的拼接

1、拼接连接字符串的工具，需导入命名空间System.Data.SqlClient

			SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = ".";
            sb.InitialCatalog = "Test";
            sb.UserID = "sa";
            sb.Password = "admin";
            string conStr = sb.ToString();

2、直接拼接连接字符串
连接字符串可以先在服务器资源管理器中添加数据库连接，然后可以在连接的属性中找到连接字符串

    string conStr1 = "Data Source=.;Initial Catalog=Test;Persist Security Info=True;User ID=sa;Password=admin";

3、拼接有占位符的字符串

• 如果参数是string类型，‘{1}’需要单引号；
• 如果是数值类型，{1}则不需要单引号；

string sql1 = string.Format("select count(*) from users where username='{0}' and " +
                    "password='{1}'", name, pwd);

三、Connection对象的创建

		//如果将创建对象写在using中，则不用关闭/销毁连接
        using (SqlConnection con = new SqlConnection(conStr1))
        {
            //注册一个con状态转换事件
            con.StateChange += Con_StateChange;
            if (con.State == System.Data.ConnectionState.Closed)
                con.Open();
            Console.WriteLine("连接通道已经打开");
        }
        Console.ReadKey();
		private static void Con_StateChange(object sender, System.Data.StateChangeEventArgs e)
        {
            //输出con现在的状态
            Console.WriteLine(e.CurrentState);
            //输出con之前的状态
            Console.WriteLine(e.OriginalState);
        }

四、连接池

连接池默认是开启的状态；
手动关闭连接池

string conStr1 = "Data Source=.;Initial Catalog=Test;" +
                "Persist Security Info=True;User ID=sa;Password=admin;pooling=false";

五、SqlCommand对象执行sql语句

1、ExecuteNonQuery()执行增删改语句

			string conStr1 = "Data Source=.;Initial Catalog=Test;Persist Security Info=True;User ID=sa;Password=admin";
            //sqlConnection对象只是用来建立连接通道
            using(SqlConnection con = new SqlConnection(conStr1))
            {
                string sql1 = "insert into Student values(45,'ee')";
                string sql2 = "delete from student where id = 1";
                string sql3 = "update student set name = '111' where id = 3";
                //创建sqlCommand对象执行sql语句
                using(SqlCommand com = new SqlCommand(sql3, con))
                {
                    con.Open();// 连接对象最晚打开最早关闭，不占用资源
                    int r = com.ExecuteNonQuery(); // 所影响的行数
                    if (r > 0)
                    {
                        Console.WriteLine("插入成功");
                    }
                }
            }
            Console.ReadKey();

2、ExecuteScalar方法执行查询，返回单行单列

• 插入并返回插入的某一列的信息，使用ExecuteScalar()执行，其返回值便是单行单列的信息；

insert into Student output inserted.列名 values ('test');

```csharp
static void Main(string[] args)
        {
            string conStr = "Data Source=.;Initial Catalog=Test;Persist Security Info=True;User ID=sa;Password=admin";
            //创建数据库连接对象
            using(SqlConnection con = new SqlConnection(conStr))
            {
                string sql = "select count(*) from Student";
                using(SqlCommand cmd = new SqlCommand(sql, con))
                {
                    //开启连接
                    con.Open();
                    //执行sql语句,ExecuteScalar()返回单行单列
                    Object o = cmd.ExecuteScalar();
                    Console.WriteLine(o);
                }
            }
            Console.ReadKey();
        }```

3、ExecuteReader()函数执行查询操作，可以返回多行

优点：每次可以返回大量数据
缺点：必须要独占一个connection

• 当遇到数据库中的空值时，通过索引器和getValue()获取的是DBNull.value,不是c#中的null，所以不会报错

        static void Main(string[] args)
        {
            string conStr = "Data Source=.;Initial Catalog=Test;Persist Security Info=True;User ID=sa;Password=admin";

            using(SqlConnection con = new SqlConnection(conStr))
            {
                string sql = "select *from Student";
                using (SqlCommand cmd = new SqlCommand(sql,con))
                {
                    con.Open();
                    //SqlDataReader可以读取大量数据，但是要独占一个connection连接
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        //每次读取一行数据
                        while (reader.Read())
                        {
                            int id = (int)reader["id"];
                            Console.Write(id);
                            Console.Write(",");
                            if (reader["name"] != DBNull.Value)
                            {
                                string name = reader["name"].ToString();
                                Console.WriteLine(name);
                            }
                            #region 通过getxxx来获取数据,对应的列是什么类型就用什么方法
                            Console.Write(reader.GetInt32(0) + "\t");
                            Console.Write(reader.IsDBNull(1)?null:reader.GetString(1));
                            #endregion 
                        }
                    }
                }
            }
            Console.ReadKey();
        }

// GetOrdinal获取指定列的索引，reader["name"]在底层调用的是reader.getValue(reader.getOridinal('name'));
 // getValue(index); 根据索引值来获取当前列的内容
int index = reader.GetOrdinal("name");

reader.GetName(索引值)； 获取对应列的列名
reader.FiledCount;  获取当前行的列数

六、参数替换防止sql注入

原理：　在使用参数化查询的情况下，数据库服务器不会将参数的内容视为SQL指令的一部份来处理，而是在数据库完成SQL指令的编译后，才套用参数运行，因此就算参数中含有指令，也不会被数据库运行。
参数在内部不是进行了简单的字符串替换，而是调用了存储过程，将参数看作一个整体来处理；

代码（除了进行了参数替换，其余代码和之前的都一样）

        static void Main(string[] args)
        {
            string name = "张三";
            string pwd = "123456";
            string conStr = "Data Source=.;Initial Catalog=Test;Persist Security Info=True;User ID=sa;Password=admin";

            using(SqlConnection con = new SqlConnection(conStr))
            {
                string sql = "select count(*) from users where username=@name and password=@pwd";
                using(SqlCommand cmd = new SqlCommand(sql, con))
                {
                    con.Open();
                    //参数替换 方法一
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@pwd", pwd);
                    // 方法二：value还可以在外边进行赋值，spm[i].value = name;此时可以结合索引来进行循环赋值； 
                    SqlParameter[] spm = new SqlParameter[]
                        {
                            new SqlParameter("@name",System.Data.SqlDbType.NChar,10){Value = name },
                            new SqlParameter("@pwd",System.Data.SqlDbType.NChar,10){Value = pwd}
                        };
                        cmd.Parameters.AddRange(spm);
                        
                    Object o = cmd.ExecuteScalar();
                    Console.WriteLine(o);
                }

            }
            Console.ReadKey();
        }

List<SqlParameter> sqlParameters = new List<SqlParameter>();
sqlParameters.Add(new SqlParameter(@name, '%' + name + '%'));

七、封装SqlHelper类

• 在新项目中引入sqlHelper----1、导入SqlHelper.cs文件；2、修改app.config；3、添加引用System.Configuration;

在app.config文件中添加连接字符串配置，以后修改连接只用在配置文件中修改即可；

<connectionStrings>
		<add name="mssqlsever" connectionString="Data Source=.;Initial Catalog=Test;Persist Security Info=True;User ID=sa;Password=admin"/>
	</connectionStrings>

先要添加引用System.Configuration
using System.Configuration;
static class SqlHelper
    {
        private static readonly string constr = ConfigurationManager.ConnectionStrings["mssqlsever"].ConnectionString;
        
        // 执行增删改查
        public static int ExecuteNonQuery(string sql,params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using(SqlCommand cmd = new SqlCommand(sql, con))
                {
                    if (pms != null)
                    {
                        // 如果参数非空，则替换参数
                        cmd.Parameters.AddRange(pms);
                    }
                    con.Open();
                    // 因为using相当于try-finally，所以return之后con一定也会被销毁
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        // 执行查询返回单行单列
        public static object ExecuteScalar(string sql,params SqlParameter[] spm)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                using(SqlCommand cmd = new SqlCommand(sql, con))
                {
                    if (spm != null)
                    {
                        cmd.Parameters.AddRange(spm);
                    }
                    con.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

        // 执行查询返回多行多列
        public static SqlDataReader ExecuteReader(string sql,params SqlParameter[] spm)
        {
            // 此处con不能用using，因为使用using会在函数return之后将con销毁，那样在外部函数中就无法对返回值进行操作，
            // 查询过程中要保证con一直是打开的状态；
            SqlConnection con = new SqlConnection(constr);
            using(SqlCommand cmd = new SqlCommand(sql, con))
            {
                if (spm != null)
                {
                    cmd.Parameters.AddRange(spm);
                }
                try
                {
                    con.Open();
                    // 参数System.Data.CommandBehavior.CloseConnection表示在ExecuteReader对象关闭后，其连接对象con也会被
                    // 一起销毁
                    return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                }
                catch
                {
                    // 此处try-catch是为了防止sql执行出现异常导致con无法销毁
                    con.Close();
                    con.Dispose();
                    throw; // 将系统异常抛出
                }
               
            }
        }
    }

• 调用ExecuteReader()方法

       private void button1_Click(object sender, EventArgs e)
        {
            List<Users> list = new List<Users>();
            string sql = "select * from users where username = @username";
            // 将参数封装进数组
            SqlParameter[] pms = new SqlParameter[]
            {
                new SqlParameter("@username",System.Data.SqlDbType.NChar,10) {Value="张三"},
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql,pms))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Users user = new Users(reader.GetString(0),reader.GetString(1));
                        list.Add(user);
                    }
                }
            }
            MessageBox.Show(list.Count.ToString());
        }

八、存储过程

存储过程是数据库中的函数，与java中的函数作用类似；在存储过程中可以使用if-else/while/insert/select等；

1、 优点：

• 执行速度快；
• 防止sql注入；
• 减少网络流通量；

2、如何执行存储过程

exec 存储过程名称；
exec sp_databases;

3、查看存储过程
exec sp_helptext ‘存储过程’；

4、自定义存储过程

• 创建

create proc usp_select
as
begin
	select * from Student
end

• 修改

alter proc usp_select
as
begin
	select * from Student
end

• 删除

drop proc usp_select;

• 带参数的存储过程

create proc usp_add_num
@n1 int,
@n2 int
as
begin
	select @n1 + @n2
end

exec usp_add_num 100,200

指定默认值的参数：若制定了默认值，则可以不给其赋值；

create proc usp_add_num
@n1 int = 100,
@n2 int
as
begin
	select @n1 + @n2
end

exec usp_add_num @n2 = 20

create proc usp_select
@name nchar(10)
as
begin
	select * from Student where name = @name
end

exec usp_select '李四'

• 带输出参数的存储过程

create proc usp_calculate
@n1 int = 10,
@n2 int output
as
begin
	set @n2 = (select count(*) from student where age>@n1)
end

// 声明实参n3
declare @n3
// 调用存储过程，并将实参n3传递给形参n2
exec usp_calculate @n2 = @n3 output;
// 打印输出n3的值
print @n3;

九、常用sql语句

• 创建一个与当前表表结构一样的新表

select top 0 * into newtable from oldtable;

• 主键id自动增长，可以不设值；但是在数据库中要将列属性中的标识规范设置为是；

十、excel与数据库的导入导出

1、NPOI使用

• 下载NPOI开发包。NPOI 1.2.3 final binary.zip
• 解压后在项目中添加对NPOI.dll与Ionic.Zip.dll（ Ionic.Zip.dll,取代旧版本中的CSharpCode.SharpZipLib.dll ）的引用。
• 描述工作簿的类：Workbook（接口）、HSSFWorkbook（具体实现类）
• 描述工作表的类：Sheet(接口)、HSSFSheet（具体实现类）

2、读取excel

• 常用方法

workbook.NumberOfSheets//获得工作表的个数。
workbook.GetSheetAt(i).GetRow(i) //获得行对象
workbook.GetSheetAt(i).GetRow(i).LastRowNum//最后一样的索引
workbook.GetSheetAt(i).GetRow(i).GetCell(j)//获得单元格对象
workbook.GetSheetAt(i).GetRow(i).GetCell(j).ToString()//获得单元格的字符串表示形式。//StringCellValue、NumericCellValue等。
workbook.GetSheetAt(i).GetRow(i).GetCell(j).CellType

• 代码

 // 读取excel中的数据到wk中
            using(FileStream fsRead = File.OpenRead("students.xls"))
            {
                
                IWorkbook wk = new HSSFWorkbook(fsRead);
                string sql = "insert into Student (name,age) values (@name,@age)";
                ISheet sheet = wk.GetSheet("Student");
                for (int i = 0; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    // 这里将所有单元格中的值都转为string，插入数据库时会自动转换
                    string id =row.GetCell(0).ToString();
                    string name = row.GetCell(1).ToString();
                    string age = row.GetCell(2).ToString();
                    // 将wk中的内容插入到数据库中
                    SqlParameter[] pms = new SqlParameter[]
                    {
                        //此处注意：自动编号id不需要插入
                        //new SqlParameter("@id",System.Data.SqlDbType.Int){Value = id },
                        new SqlParameter("@name",System.Data.SqlDbType.NChar,10){Value = name },
                        new SqlParameter("@age",System.Data.SqlDbType.Int){Value = age }
                    };
                    SqlHelper.ExecuteNonQuery(sql, CommandType.Text,pms);
                }
            }

3、写入excel

• 常用方法

wkbook.CreateSheet("test").CreateRow(index).CreateCell(index).SetCellValue("内容")；

• 代码

			 IWorkbook wkbook = new HSSFWorkbook();
            string sql = "select * from Student";
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql,CommandType.Text))
            {
                if (reader.HasRows)
                {
                    ISheet sheet = wkbook.CreateSheet("student");
                    int i = 0;
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        string name = reader.IsDBNull(1) ? null : reader.GetString(1);
                        int age = reader.GetInt32(2);
                        IRow row = sheet.CreateRow(i++);
                        row.CreateCell(0).SetCellValue(id);
                        row.CreateCell(1).SetCellValue(name);
                        row.CreateCell(2).SetCellValue(age);
                    }
                }
            }
            using(FileStream fsWrite = File.OpenWrite("students.xls"))
            {
                wkbook.Write(fsWrite);
            }
            MessageBox.Show("输出完毕");

4、db到excel

 private void button1_Click(object sender, EventArgs e)
        {
            //1、通过sqlHelper读取数据
            string sql = "select * from Table1";
            using(SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text, null))
            {
                if (reader.HasRows)
                {
                    // 创建工作簿
                    IWorkbook wkbook = new HSSFWorkbook();
                    // 通过工作簿来创建工作表
                    ISheet sheet = wkbook.CreateSheet("list");
                    //-----------------------------
                    // 第一行设置为列名
                    IRow row = sheet.CreateRow(0);
                    for (int j = 0; j < reader.FieldCount; j++)
                    {
                        row.CreateCell(j).SetCellValue(reader.GetName(j));
                    }
                    
                    //-----------------------------
                    int i = 1; // 数据行从1开始，第0行是列名
                    while (reader.Read())
                    {
                        //读取数据
                        int id = reader.GetInt32(0);
                        string name = reader.GetString(1);
                        int age = reader.GetInt32(2);
                        // 类型? 表示可为null的类型；
                        // 对于存在null的数据处理
                        DateTime? date = reader.IsDBNull(3) ? null : (DateTime?)reader.GetDateTime(3);
                        int? test = reader.IsDBNull(4) ? null : (int?)reader.GetInt32(4);

                        //通过工作簿来创建row
                        IRow row1 = sheet.CreateRow(i++);
                        row1.CreateCell(0).SetCellValue(id);
                        row1.CreateCell(1).SetCellValue(name);
                        row1.CreateCell(2).SetCellValue(age);
                        if (date == null)
                        {
                            // 如果为空，则将单元格类型设置为空
                            row1.CreateCell(3).SetCellType(CellType.BLANK);
                        }
                        else
                        {
                            //创建单元格
                            ICell cell = row1.CreateCell(3);
                            //创建单元格样式
                            ICellStyle cellstyle = wkbook.CreateCellStyle();
                            cellstyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
                            // 设置单元格样式
                            cell.CellStyle = cellstyle;
                            //设置单元格内容
                            cell.SetCellValue((DateTime)date);
                        }
                        if (test == null)
                        {
                            row1.CreateCell(4).SetCellType(CellType.BLANK);
                        }
                        else
                        {
                            row1.CreateCell(4).SetCellValue((int)test);
                        }
                        
                    }
                    using (FileStream fsWrite = File.OpenWrite("list.xls"))
                    {
                        wkbook.Write(fsWrite);
                        MessageBox.Show("写入成功   "+ DateTime.Now.ToString("yy-MM-dd HH:mm:ss"));
                    }
                }
                
            }

        }

5、excel到db

private void button2_Click(object sender, EventArgs e)
        {
            // 读取excel中的数据
            using(FileStream fsRead = File.OpenRead("list.xls"))
            {
                // 这里跟向excel中写入不同，直接在构造函数中传入fsRead
                IWorkbook wkbook = new HSSFWorkbook(fsRead);
                ISheet sheet = wkbook.GetSheet("list");
                // 主键id自动增长，可以不设值；但是在数据库中要将列属性中的标识规范设置为是；
                string sql = "insert into table2 (name,age,time,test) values(@name,@age,@time,@test)";
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    // 创建参数对象
                    SqlParameter[] pms = new SqlParameter[]
                    {
                        new SqlParameter("@name",SqlDbType.NChar,10),
                        new SqlParameter("@age",SqlDbType.Int),
                        new SqlParameter("@time",SqlDbType.DateTime),
                        new SqlParameter("@test",SqlDbType.Int)
                    };
                    
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        // 从第二个单元格开始读取，第一个单元格中存放的是id，不用读取；
                        for (int j = 1; j < row.LastCellNum; j++)
                        {
                            ICell cell = row.GetCell(j);
                            if(cell!=null && cell.CellType != CellType.BLANK)
                            {
                                // 日期类型的值要特殊考虑
                                if (j == 3)
                                {
                                   // 日期类型如何转换 
                                  DateTime d = DateTime.FromOADate(cell.NumericCellValue);
                                   pms[j - 1].Value = d;
                                }
                                else
                                {
                                    pms[j - 1].Value = cell.ToString();
                                }
                                
                            }
                            else
                            {
                                // DBNull.Value表示db中的null
                                pms[j - 1].Value = DBNull.Value;
                            }
                        }
                    }
                    SqlHelper.ExecuteNonQuery(sql, CommandType.Text, pms);
                }
            }
            //DateTime.Now.ToString("yy-MM-dd HH:mm:ss") 打印当前时间
            MessageBox.Show("写入成功   " + DateTime.Now.ToString("yy-MM-dd HH:mm:ss"));
        }

十一、DataSet和DataTable

相关描述

• DataSet相当于内存中的数据库，而DataTable是DataSet中的表；

十二、从db中递归加载TreeView

• 方法一：先将数据读取到datatable中，然后通过操作datatable来获取数据加载到TreeView中

       private void Form1_Load(object sender, EventArgs e)
        {
            LoadTreeView(treeView1.Nodes,0);
        }

        private void LoadTreeView(TreeNodeCollection node,int pid)
        {
            DataTable dt = getDataTable(pid);
            // 要点一：如何遍历dt
            // dt.Rows代表数据库中的所有行，而item代表每一行，item[0]代表第一列数据
            foreach (DataRow item in dt.Rows)
            {
                int id = Convert.ToInt32(item[0]);
                TreeNode n = node.Add(item[1].ToString());
                LoadTreeView(n.Nodes, id);
            }
            
        }

        /// <summary>
        /// 将数据库中查询到的数据存放到内存中的DataTable中，通过在内存中操作datatable来获取数据
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        private DataTable getDataTable(int pid)
        {
            string sql = "select id,name from TabArea where pid = @pid";
            // 要点二：传递的sqlparameter不一定是数组
            return SqlHelper.ExecuteDataTable(sql, CommandType.Text, 
                new System.Data.SqlClient.SqlParameter("@pid",SqlDbType.Int){Value=pid} );
        }

• 方法二：使用datareader,也是将数据暂存到datareader对象中，通过操作dataReader将数据加载到TreeView中，和方法一本质上是相同的

        private void LoadTreeView1(TreeNodeCollection node, int pid)
        {
            SqlDataReader reader = getDataTable(pid);
            while (reader.Read())
            {
                int id = reader.GetInt32(0);
                string name = reader.GetString(1);
                TreeNode tn = node.Add(name);
                LoadTreeView1(tn.Nodes, id);
            }
        }