C#中ExecuteReader、ExecuteNonQuery、ExecuteScalar、SqlDataReader、SqlDataAdapter和DataTable的使用
来源：互联网 发布：hsv颜色模型算法实现 编辑：程序博客网 时间：2024/06/06 20:04
一、数据库连接字符串
private readonly static string connectionString = ConfigurationManager.ConnectionStrings["MyConnection"].ToString(); //web.config中配置连接语句
二、ExecuteNonQuery：执行增，删，改的方法，支持存储过程
- /// 连接字符串，自满足，请用SqlHelper.connString赋值
- /// 参数1：命令类型，如果是sql语句，则为CommandType.Text,否则为 CommandType.StoredProcdure
- /// 参数2：SQL语句或者存储过程名称
- /// 参数3：SQL参数，如果没有参数，则为null
- /// 返回：受影响的行数
- public static int ExecuteNonQuery(CommandType commandType, string cmdText, params SqlParameter[] para)
- {
- using (SqlConnection conn = new SqlConnection(connectionString))
- {
- SqlCommand cmd = new SqlCommand();
- cmd.Connection = conn;
- cmd.CommandType = commandType;
- cmd.CommandText = cmdText;
- if (para != null)
- {
- cmd.Parameters.AddRange(para);
- }
- conn.Open();
- return Convert.ToInt32(cmd.ExecuteNonQuery());
- }
- }
三、GetScalar：执行查询的方法，支持存储过程
/// 参数1：命令类型，如果是sql语句，则为CommandType.Text,否则为 CommandType.StoredProcdure
/// 参数2：SQL语句或者存储过程名称
/// 参数3：SQL参数，如果没有参数，则为null
/// 返回：查询结果的第一行第一列
- <pre name="code" class="html"> public static object GetScalar(CommandType commandType, string cmdText, params SqlParameter[] para)
- {
- object result = null;
- using (SqlConnection conn = new SqlConnection(connectionString))
- {
- SqlCommand cmd = new SqlCommand();
- cmd.Connection = conn;
- cmd.CommandType = commandType;
- cmd.CommandText = cmdText;
- if (para != null)
- {
- cmd.Parameters.AddRange(para);
- }
- conn.Open();
- result = cmd.ExecuteScalar();
- }
- return result;
- }
- }
- <pre name="code" class="html"> public static SqlDataReader ExecuteReader(CommandType commandType, string cmdText, params SqlParameter[] para)
- {
- SqlConnection conn = new SqlConnection(connectionString);
- SqlDataReader dr = null;
- SqlCommand cmd = new SqlCommand();
- cmd.Connection = conn;
- cmd.CommandType = commandType;
- cmd.CommandText = cmdText;
- if (para != null)
- {
- cmd.Parameters.AddRange(para);
- }
- conn.Open();
- dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
- return dr;
- public static DataSet GetDataSet(CommandType commandType, string cmdText, params SqlParameter[] para)
- {
- using (SqlConnection conn = new SqlConnection(connectionString))
- {
- SqlDataAdapter da = new SqlDataAdapter();
- da.SelectCommand = new SqlCommand();
- da.SelectCommand.Connection = conn;
- da.SelectCommand.CommandText = cmdText;
- da.SelectCommand.CommandType = commandType;
- if (para != null)
- {
- da.SelectCommand.Parameters.AddRange(para);
- }
- DataSet ds = new DataSet();
- conn.Open();
- da.Fill(ds);
- return ds;
- }
- }
六、SqlDataReader与SqlDataAdapter+DataSet的区别1、SqlDataReader //基于连接，只读访问，适合数据量较小。（连接模式） SqlDataAdapter //基于非连接，适于数据量较大时，可以另行修改，最后再把修改结果返回给数据库。要求资源也大一点 （断开模式）2、SqlDataAdapter 读取数据后将数据集放入DataSet，DataSet 的数据存在本地客服机内存。3、SqlDataReader返回的是一个数据读写器，只能一条条的读，操作起来不灵活，一般在只读的时候才用到。 SqlDataAdapter返回的是数据集或者表，可以对其中的数据作任意操作4、写法上不同： （1）SqlDatReader执行前须先打开数据库，然后须生成一个command对象。再由command.ExecuteReader()方法赋值。完成后须手动关闭联接。- SqlCommand cmd = new SqlCommand("select * from stu", conn);
- conn.Open();
- SqlDataReader rdr = cmd.ExecuteReader();
- ..................
- conn.close();
- SqlDataAdapter adptr = new SqlDataAdapter(sql, conn);
- DataSet ds = new DataSet();
- adptr.Fill(ds, "stu");<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);"> </span>
七、实例：
1、运用SqlDataReader 读取数据- class DataReader
- {
- static void Main()
- {
- string str = "server=localhost;uid=sa;pwd=123;database=northwind";
- SqlConnection conn = new SqlConnection(str);
- SqlCommand cmd = new SqlCommand("select * from stu", conn);
- conn.Open();
- SqlDataReader rdr = cmd.ExecuteReader();
- DataTable table=new Datable();
- table.Load(rdr);
- rdr.Close();
- conn.Close();
- }
- }
- class SqlDataAdapter
- {
- static void Main()
- {
- string str = "server=localhost;uid=sa;pwd=123;database=northwind";
- SqlConnection conn = new SqlConnection(str);
- string sql = "select * from stu";
- SqlDataAdapter adptr = new SqlDataAdapter(sql, conn);//Adepter对象
- DataSet ds = new DataSet();//DataSet对象
- adptr.Fill(ds, "stu");//填充DataSet 并为当前表命名
- DataTableReader rdr = ds.CreateDataReader();
- while (rdr.Read())//读取表中数据
- {
- for (int i = 0; i < rdr.FieldCount; i++)
- {
- Console.Write(rdr.GetName(i) + "\t" + rdr.GetValue(i) + "\t");
- }
- Console.WriteLine();
- }
- }
- }
八、DataTable的使用
http://holysonll.blog.163.com/blog/static/214139093201352011491680/
- /// <summary>
- /// 获取车辆数据：车辆数和运力
- /// </summary>
- /// <returns></returns>
- public IList<RoadTransData> GetVehicleData()
- {
- StringBuilder sb = new StringBuilder();
- sb.Append(" select 'data1_Pass' as dataTitle, COUNT(*) as vehicleNum ,SUM(PassengerSeats) as apprLoadMass ");
- sb.Append(" from RT_VehiclePassenger A, RT_Vehicle V ");
- sb.Append(" where A.VehicleId = V.VehicleId and V.CertState =1 ");
- sb.Append(" UNION ");
- sb.Append(" select 'data2_Goods' as dataTitle, COUNT(*) as vehicleNum ,SUM(A.ApprLoadMass) as apprLoadMass ");
- sb.Append(" from RT_VehicleGoods A, RT_Vehicle V ");
- sb.Append(" where A.VehicleId = V.VehicleId and V.CertState =1 ");
-
- CommonService cs = new CommonService();
- DataTable dt = cs.GetDataTableBySQL(sb.ToString());
-
- IList<RoadTransData> lstRoadTransData = new List<RoadTransData>();
-
- for (int i = 0; i < dt.Rows.Count; i++)
- {
- RoadTransData objRoadTransData = new RoadTransData();
- objRoadTransData.RptName = dt.Rows[i][0].ToString();
- objRoadTransData.Data1 = dt.Rows[i][1] == null ? 0 : Convert.ToDouble(dt.Rows[i][1].ToString());
- objRoadTransData.Data2 = dt.Rows[i][2] == null ? 0 : Convert.ToDouble(dt.Rows[i][2].ToString());
- lstRoadTransData.Add(objRoadTransData);
- }
-
- return lstRoadTransData;
- }
