C# ADO.net连接数据库 进行增删改查操作

ADO.NET数据库操作图

数据库连接语句

static string sqlCon = @"server =.;database=strike;Integrated Security=True";

        /// <summary>
        /// 连接测试
        /// </summary>
        private static void TestConnection()
        {
            //string conStr = @"server=.;database=strike;Integrated Security=True;";
            SqlConnection conn = new SqlConnection(sqlCon);
            conn.Open();
            conn.Close();
            Console.WriteLine("连接对象新建成功。");
            Console.ReadKey();
        }

1增删改

增删改都是类似的操作，使用SqlCommand进行，

都是执行非查询语句ExecuteNonQuery()进行数据库操作，只是sql语句有点不同。

新增记录

        /// <summary>
        /// 新增记录
        /// </summary>
        public static void AddRecord()
        {
            int res = -1;
            //string sqlCon = "server =.;database=strike;Integrated Security=True";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlCon;
            string b_title = "标";
            string b_content = "容";
            string b_author = "作";
            string addtime = "2011-11-11";
            int b_cid = 102;
            string sqlStr2 = string.Format("insert book(b_cid,b_title,b_content,b_author,b_isdel,b_addtile,b_money) values({0},'{1}','{2}','{3}',0,'{4}',10)", b_cid, b_title, b_content, b_author, addtime);
            string sqlStr = "insert book(b_cid,b_title,b_content,b_author,b_isdel,b_addtile,b_money) values(103,'dsf','dfdf','dfd',0,'2011-11-11',10)";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = sqlStr2;
            conn.Open();
            res = cmd.ExecuteNonQuery();
            conn.Close();
            if (res > 0)
            {
                Console.WriteLine("受影响" + res);
            }
        }

删除数据

        /// <summary>
        /// 删除数据
        /// </summary>
        public static void DelRecord()
        {
            int res = -1;
            //string sqlCon = "server =.;database=strike;Integrated Security=True";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlCon;
            string sqlStr = "delete book where b_id=18";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            conn.Open();
            res = cmd.ExecuteNonQuery();
            conn.Close();
            if (res > 0)
            {
                Console.WriteLine(res + "行受影响");
            }
        }

更新操作

        /// <summary>
        /// 软删除
        /// </summary>
        public static void SoftDelRecord()
        {
            int res = -1;
            //  string sqlCon = @"server =.;database=strike;Integrated Security=True";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlCon;
            string sqlStr = "update book set b_isdel=1 where b_cid=103";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            conn.Open();
            res = cmd.ExecuteNonQuery();
            conn.Close();
            if (res > 0)
            {
                Console.WriteLine(res + "行受影响");
            }
        }

2查询操作

1）SqlCommand查询单个值

用ExecuteScalar（）方法  ，返回单个值(Object)（查询结果第一行第一列的值）

        public static void QuerySingle()
        {
            SqlConnection conn = new SqlConnection(sqlCon);
            string sqlStr = "select * from book";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            conn.Open();

            //返回的是查询结果集第一单元格的值，返回的是object类型
            object obj = cmd.ExecuteScalar();
            conn.Close();
            Console.WriteLine(obj.ToString());
        }

2）SqlCommand查询多行

用ExecuteReader()方法

        /// <summary>
        /// 查询多行数据---DataReader逐行读取,每次读一行
        /// </summary>
        public static void QueryList()
        {
            SqlConnection con = new SqlConnection(sqlCon);
            string sqlStr = "select * from book";
            SqlCommand cmd = new SqlCommand(sqlStr, con);
            con.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())//dr每read（）一次就读取一行
                {

                    //read()后，dr就是一行数据
                    object o = dr["b_title"];
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        Console.Write(dr[i].ToString() + "\t");
                        int a = dr.GetInt32(0);

                    }
                    Console.Write("\n");


                }
                // Console.WriteLine("有数据");
            }
            else
            {
                Console.WriteLine("无数据");
            }
        }

3）使用适配器读取数据

        public static void QueryListByAdapter()
        {
            SqlConnection conn = new SqlConnection(sqlCon);

            string sqlStr = "select * from book";
            //创建适配器对象，高数它要做些什么事情，走哪条路去
            SqlDataAdapter da = new SqlDataAdapter(sqlStr, conn);
            // SqlDataAdapter da = new SqlDataAdapter();

            //创建数据集对象（程序的临时数据库）
            DataSet ds = new DataSet();

            DataTable dt1 = new DataTable();
            //调用fill方法，填充数据集。
            da.Fill(dt1);
            da.Fill(ds);

            //获得数据集的第一张表
            DataTable dt = ds.Tables[0];

            //循环数据表的每一行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];//每一行付给dr
                Console.WriteLine(dr[0].ToString() + "___" + dr["b_title"].ToString());
            }

        }

4）调用存储过程

        public static void QueryListByProc2()
        {
            string sqlCon = "server=.;database=strike;Integrated Security=True;";
            SqlConnection conn = new SqlConnection(sqlCon);
            SqlCommand cmd = new SqlCommand("uspo_GetPageBooks", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] paras ={
                                      new SqlParameter("@pageIndex",SqlDbType.Int,4),
                                      new SqlParameter("@pageSize",SqlDbType.Int,4)
                                   };
            paras[0].Value = 1;
            paras[1].Value = 3;
            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() + "__" + dr[2].ToString());
            }

AOD.net的基本操作就这些，要掌握好以后还要多写多练，问google大神。