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); }
增删查改常用代码
原创
©著作权归作者所有:来自51CTO博客作者蓬莱仙羽的原创作品,请联系作者获取转载授权,否则将追究法律责任
上一篇:OC学习笔记[注意事项]
下一篇:C++构造和析构以及虚函数应用
![](https://ucenter.51cto.com/images/noavatar_middle.gif)
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
Android studio 记事本项目(增删改查功能完整代码)
安卓记事本项目(增删改查)
android xml android studio 安卓 -
springboot增删改查一套Service模板
springboot一套
ide Code ci