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

1.SqlDataReader  （流读取一次一条记录）

如果查询整个表或者多列，可以对字段所在列的位置重新命名，而不必知道具体在哪一列

                    SqlDataReader sdr = cmd.ExecuteReader();
                    int i = sdr.GetOrdinal("ACCOUNT_ID");  //获取序号值

                    Console.WriteLine(i); 

                  while (sdr.Read())
                    {

                   Console.WriteLine(sdr[0] + " " + sdr[1]+"     "+sdr[i]);

                  }

                Console.ReadLine();

取得数据的多种方法

新建类，使用列表

 class Catagory
    {
        private int amount;
        private string catagoryName;
        private int accountID;

        public Catagory() { }

        public int Amount    //VS2005简单的写为public int Amount{get;set;}会出错
        {
            get
            {
                return amount;
            }
            set
            {
                amount = value;
            }
        }
        public string CatagoryName {
            get
            {
                return catagoryName;
            }
            set
            {
                catagoryName = value;
            }
        }
        public int AccountID {
            get
            {
                return accountID;
            }
            set
            {
                accountID = value;
            }
        }
    }

从列表取得，并转换数据

 List<Catagory> cateList = new List<Catagory>();  构建类列表
                    while (sdr.Read())
                    {
                        Catagory cate = new Catagory();
                        //cate.AccountID = Int32.Parse(sdr[2].ToString()); //数据转换，数值和字符串
                        //cate.Amount = Int32.Parse(sdr[1].ToString());
                        //cate.CatagoryName = sdr[0].ToString();
                        cate.AccountID = sdr.GetInt32(2);  //此种转换方法最简洁
                        cate.Amount = sdr.GetInt32(1);
                        //if (sdr.IsDBNull(0)) //使用IsDBNull判断空值
                        //{
                        //    cate.CatagoryName = "null";
                        //}
                        if (System.DBNull.Value == sdr[0])
                        {
                            cate.CatagoryName = "null";  //使用System.DBNull.Value 判断空值
                        }
                        else
                        {
                            cate.CatagoryName = sdr.GetString(0); //如果数据不为空值

                        }                        
                        cateList.Add(cate); //数据加入到列表
                        //Console.WriteLine(sdr[0] + " " + sdr[1]+"     "+sdr[i]); //最简单的读取
                    }
                    foreach (Catagory temp in cateList)  //遍历整个列表
                    {
                        Console.WriteLine(temp.CatagoryName+"   "+temp.Amount+"     "+temp.AccountID);
                    }
                }
            }
            Console.ReadLine();

2.多结果集（默认只有一个）

使用MulteipleActiveResultSets=true，加在连接字符串后面，支持SQL2005以上版本

                    SqlCommand cmd2 = con.CreateCommand();  //对下一个结果集的处理
                    cmd2.CommandText = "select NAME,ADDRESS from COMPANY;";
                    SqlDataReader sdr2 = cmd2.ExecuteReader();
                    while (sdr2.Read())
                    {
                        Console.WriteLine(sdr2[0]+"   "+sdr2[1]);
                    }

3.参数化查询

针对选择查询不在语句中限定查询条件值，而是写入得到的情况，有以下3种方法：

1）int accountID = Convert.ToInt32(Console.ReadLine());  //输入查询条件，需转换为与查询字段相同的类型

cmd.CommandText = "select NAME,AMOUNT from BILLS where ACCOUNT_ID='"+accountID+"'";  //容易出错，不建议采用

2）cmd.CommandText = "select NAME,AMOUNT from BILLS where ACCOUNT_ID=@ACCOUNT_ID";

SqlParameter param = new SqlParameter();
param.ParameterName = "ACCOUNT_ID";
param.SqlDbType = System.Data.SqlDbType.Int;
param.Value = accountID;
 cmd.Parameters.Add(param);

3）cmd.CommandText = "select NAME,AMOUNT from BILLS where ACCOUNT_ID=@ACCOUNT_ID";

cmd.Parameters.AddWithValue("@ACCOUNT_ID",accountID); //最佳方法，需要System.Data命名空间