增删查改常用代码

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);
        }