static string sqlcon = "server=.;database=;Integrated Security=true;"; /// <summary> /// 新增数据 /// </summary> public static void Add() { //string sqlcon = "server=.;database=;Integrated Security=true;"; SqlConnection conn=new SqlConnection(sqlcon); string sqlStr="insert into Boook(b_id,b_title,b_author,b_money)values(1,'ASP.NET'，'莫言',100)"; SqlCommand cmd=new SqlCommand(sqlStr,conn); conn.Open(); int i = cmd.ExecuteNonQuery(); conn.Close(); if (i>0) { Console.Write("新增成功！"); } else { Console.Write("新增失败！"); } } /// <summary> /// 删除数据 /// </summary> public static void Delete() { //string sqlcon = "server=.;database=;Integrated Security=true;"; SqlConnection conn = new SqlConnection(sqlcon); string sqlStr = "delete from book where b_id=1"; SqlCommand cmd = new SqlCommand(sqlStr,conn); conn.Open(); int i = cmd.ExecuteNonQuery(); conn.Close(); if (i > 0) { Console.Write("删除成功！"); } else { Console.Write("删除失败！"); } } /// <summary> /// 数据更新，软删除 /// </summary> private static void Update() { //string sqlcon = "server=.;database=;Integrated Security=true;"; SqlConnection conn = new SqlConnection(sqlcon); string sqlStr = "update Boook set b_id =2 where b_id=1"; SqlCommand cmd = new SqlCommand(sqlStr, conn); conn.Open(); int i = cmd.ExecuteNonQuery(); conn.Close(); if (i > 0) { Console.Write("更新成功！"); } else { Console.Write("更新失败！"); } } /// <summary> /// 读取单个值 /// </summary> private static void selectSingle() { SqlConnection conn = new SqlConnection(sqlcon); string sqlStr = "select * from Book"; SqlCommand cmd = new SqlCommand(sqlStr, conn); conn.Open(); object obj = cmd.ExecuteScalar(); conn.Close(); Console.Write(obj.ToString()); } /// <summary> /// dateReader读取数据，逐行读取，通过下表访问列 /// </summary> private static void dateReader() { SqlConnection conn = new SqlConnection(sqlcon); string sqlStr = "select * from Book"; SqlCommand cmd = new SqlCommand(sqlStr, conn); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read())//如果读到下一行数据就返回True，且本身就属于那一行数据 { Console.Write(dr[0].ToString() + '_' + dr[1].ToString()+'_'+dr["ID"].ToString()); } } else { Console.Write("无数据"); } dr.Close(); conn.Close(); } //使用适配器填充数据集 SqlDataAdapter不需要手动开关，它能够自己开关 public static void QueryListAdapter() { SqlConnection con = new SqlConnection(sqlcon); string sqlStr = "select*from book"; SqlDataAdapter da = new SqlDataAdapter(sqlStr,con); DataSet ds = new DataSet(); da.Fill(ds); DataTable dt = ds.Tables[0]; //循环数据表中的每一行 for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i];//将表中的一行拿出来给行对象 Console.WriteLine(dr[0].ToString() + "_" + dr["ID"].ToString()); } } //使用适配器填充数据集 SqlDataAdapter不需要手动开关，它能够自己开关 public static void QueryListAdapter2() { SqlConnection con = new SqlConnection(sqlcon); string sqlStr = "select*from book"; SqlDataAdapter da = new SqlDataAdapter(sqlStr, con); DataSet ds = new DataSet(); da.Fill(ds); DataTable dt = ds.Tables[0]; //循环数据表中的每一行 for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i];//将表中的一行拿出来给行对象 Console.WriteLine(dr[0].ToString() + "_" + dr["ID"].ToString()); } } //调用存储过程查询数据 public static void QuerListByProc() { SqlConnection conn = new SqlConnection(sqlcon); SqlCommand cmd=new SqlCommand("usp_GetBookMyCateId",conn); //无参数的存储过程 SqlParameter sp2 = new SqlParameter(); sp2.ParameterName = "@cateId"; sp2.SqlDbType = SqlDbType.Int; sp2.Value = 2; cmd.Parameters.Add(sp2); //有两个参数的存储过程 SqlParameter sp = new SqlParameter("@cateId", 2); cmd.Parameters.Add(sp); SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(); da.Fill(dt); foreach(DataRow dr in dt.Rows) { Console.WriteLine(dr[0].ToString() + "_" + dr["ID"].ToString()); } } //调用多个参数的存储过程查询 private static void QueryListByProc2() { SqlConnection conn = new SqlConnection(sqlcon); SqlCommand cmd = new SqlCommand("proGetPageData", conn); cmd.CommandType = CommandType.StoredProcedure; //尽量不要使用两个参数的存储过程，类型是枚举类型，另外一个两个参数的函数值混淆 //SqlParameter par = new SqlParameter("@Id", DbType.Int32); //SqlParameter par = new SqlParameter("@id", 11); //赋值多个参数 SqlParameter[] paras ={ new SqlParameter("@pageIndex",SqlDbType.Int,4),//这里的4是代表整型的长度 new SqlParameter("@pageSize",SqlDbType.Int,4) }; //cmd.Parameters.AddRange(paras); paras[0].Value = 1;//搜索第一页 paras[1].Value = 2;//赋值的 cmd.Parameters.AddRange(paras);//为command对象添加pameters数组 conn.Open(); //SqlDataReader dr = cmd.ExecuteReader(); //while (dr.Read()) //{ // Console.Write("id=" + dr[0].ToString()); //} //dr.Close(); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { Console.WriteLine(dr[0].ToString() + "_" + dr[1].ToString()); } conn.Close(); } //调用带输出参数的存数过程 private static void QuerListProc3() { SqlConnection conn = new SqlConnection(sqlcon); SqlCommand cmd = new SqlCommand("proGetData2", conn); SqlParameter[] paras ={ new SqlParameter("@pageIndex",SqlDbType.Int), new SqlParameter("@pageSize",SqlDbType.Int), new SqlParameter("@pageCount",SqlDbType.Int), new SqlParameter("@rowCount",SqlDbType.Int) }; paras[0].Value = 1; paras[1].Value = 2; paras[2].Direction = ParameterDirection.Output; paras[3].Direction = ParameterDirection.Output;//设置参数的输出方向 cmd.Parameters.AddRange(paras); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { Console.WriteLine(dr[0].ToString() + "_" + dr[1].ToString()); } int pageCount=Convert.ToInt32(cmd.Parameters[2].Value); int rowCount=Convert.ToInt32(cmd.Parameters[3].Value);//获取输出参数 Console.WriteLine("pageCount=" + pageCount + ",rowCount=" + rowCount); }