ADO.NET知识汇总

这又是一篇记录平常工作笔记的博客，无论是在排版还是解说上都不会有太多要求。同时这也是一篇不上博客园首页的博客，Just记录一些工作笔记。

vSelect返回单个值

            string connSQL = @"Data Source=Test; Initial Catalog=DBName; Integrated Security=SSPI";
            using (SqlConnection conn = new SqlConnection(connSQL))
            {
                string strSQL = "select count(*) from [dbo].[TableName]";
                SqlCommand cmd = new SqlCommand(strSQL, conn);//创建Command对象 

                try
                {
                    conn.Open();//一定要注意打开连接
                    int rows = (int)cmd.ExecuteScalar();//执行命令
                    Console.WriteLine("执行ExcuteScalar方法：共{0}行记录", rows);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("\nError：\n{0}", ex.Message);
                }
            }

            Console.Read();

vSqlDataReader To DataTable

        static void Main(string[] args)
        {
            var sourceData = GetInfo();
            UserInfo userInfo = null;
            foreach (DataRow item in sourceData.Rows)
            {
                userInfo = new UserInfo()
                {
                    Id=Convert.ToInt32(item["Id"].ToString()),
                    UserName = item["UserName"].ToString()
                };
            }
            Console.Read();
        }

        static DataTable GetInfo()
        {
            string connSQL = @"Data Source=Test; Initial Catalog=DBName; Integrated Security=SSPI";
            string strSQL = "select * from [dbo].[User]";
            DataTable myDataTable = new DataTable();
            using (SqlConnection conn = new SqlConnection(connSQL))
            {
                SqlCommand command = new SqlCommand(strSQL, conn);
                conn.Open();
                SqlDataReader reader = command.ExecuteReader();
                myDataTable.Load(reader);
                reader.Close();
            }

            return myDataTable;
        }

v增加一条数据

        static void Main(string[] args)
        {
            string connSQL = @"Data Source=.\SQLEXPRESS; Initial Catalog=db_MyDemo; Integrated Security=SSPI";//构造连接字符串
            SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder(connSQL);

            using(SqlConnection conn = new SqlConnection(connStr.ConnectionString))
            {
                //拼接SQL语句
                  StringBuilder strSQL = new StringBuilder();
                strSQL.Append("insert into tb_SelCustomer ");
                strSQL.Append("values(");
                strSQL.Append("'liuhao','0','0','13822223333','[e-mail]','广东省深圳市宝安区',12.234556,34.222234,'422900','备注信息')");

                Console.WriteLine("Output SQL:\n{0}",strSQL.ToString());

                //创建Command对象
                  SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSQL.ToString();

                try
                {
                    conn.Open();//一定要注意打开连接

                       int rows = cmd.ExecuteNonQuery();//执行命令
                       Console.WriteLine("\nResult: {0}行受影响",rows);
                }
                catch(Exception ex)
                {
                    Console.WriteLine("\nError：\n{0}", ex.Message);
                }
            }

            Console.Read();
        }

vSqlDataAdapter Select

            string connSQL = @"Data Source=Test; Initial Catalog=DBName; Integrated Security=SSPI";
            using (SqlConnection conn = new SqlConnection(connSQL))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                string strSQL = "select * from [dbo].[User]";
                adapter.SelectCommand = new SqlCommand(strSQL, conn);
                DataSet dataset = new DataSet();
                adapter.Fill(dataset);
                DataTable dt = dataset.Tables[];
            }

SqlDataAdapter 还包括 SelectCommand、InsertCommand、DeleteCommand、UpdateCommand 和 TableMappings 属性，以便于数据的加载和更新。