sql server基本编程

前言

C#操作sqlserver跟操作其他数据没有太大差别，但是又一些细节要注意一下。

在安装sqlserver时不要选择默认实例，如果是则需要更改设置，还有远程连接要去连接服务中设置一下，如端口1433等，这些百度一下就可以了。下面开始介绍一些最基本的数据库操作。

1.0 建立数据库

static void SQLDBCreate(string dbname)
        {
            //通过连接到本身存在的db  master连接到sqlserver
            string connStr = "Server = localhost;User Id=sa;Password=******; database = master";
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            //如果当前数据库是正在状态是无法删除的，所以把master设置为当前状态，若数据库存在则删除重建
            //此处出问题一般为数据安装过程中为默认实例，改为特定实例，然后启动服务中心，在ip3更改相关ip为本机，并把tcp端口改为1433
            //其他更改为是，同时在服务中restart即可以
            string sql= "use master;" +
            "IF DB_ID(N'{0}') IS NOT NULL " +
            "DROP DATABASE {0};" +
                "CREATE DATABASE {0}";
            sql = string.Format(sql, dbname);

            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                Console.WriteLine("createdb_success");
            }
            catch (Exception ex)
            {
                Console.WriteLine("createdb_failed:"+ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

1.1 建立的数据库中Table

static void TableCreate(string dbname,string tablename)
        {
            string connStr = "Server = localhost;User Id=sa;Password=********; database = {0}";
            connStr = string.Format(connStr, dbname);
            string sql= "IF OBJECT_ID(N'{1}..{0}', N'U') IS NOT NULL " +
                 "DROP TABLE {0};" +
                "CREATE TABLE {0}" +
                "(ID [int] NOT NULL Primary Key," +
                "Name [char] (40) NULL ," +
                "Price [char] (40) NULL," +
            "dPrice [char] (40) NULL)";
            sql = string.Format(sql, tablename, dbname);
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();

            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                Console.WriteLine("createtable_success");
            }
            catch(Exception ex)
            {
                Console.WriteLine("createtable_failed:" + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

1.2 向表中添加数据

static void FillTable(string dbname,string tablename)
        {
            string connStr = "Server = localhost;User Id=sa;Password=********; database = {0}";
            connStr = string.Format(connStr, dbname);
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            string sql= "INSERT INTO {0}(ID,name,price,dPrice)" +
                "values(@d,@a,@b,@c)";
            sql = string.Format(sql, tablename);
            try
            {
                for(int i=0;i<100;i++)
                {
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@d", i);
                    cmd.Parameters.AddWithValue("@a", Guid.NewGuid());
                    cmd.Parameters.AddWithValue("@b", i.ToString());
                    cmd.Parameters.AddWithValue("@c", i.ToString());
                    cmd.ExecuteNonQuery();                    
                }
                Console.WriteLine("filltable_success");
            }
            catch(Exception ex)
            {
                Console.WriteLine("filltable_failed:" + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

1.3 删除表格

static void DeleteTable(string dbname,string tablename)
        {
            string connStr = "Server = localhost;User Id=sa;Password=*********; database = {0}";
            connStr = string.Format(connStr, dbname);
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            string sql= "IF OBJECT_ID(N'{0}..{1}', N'U') IS NOT NULL " +
                 "DROP TABLE {1};";
            sql = string.Format(sql, dbname, tablename);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                Console.WriteLine("deletetable_success");
            }
            catch(Exception ex)
            {
                Console.WriteLine("deletetable_failed:" + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

1.4 读取数据库中数据

static void TransToDS(string dbname,string tablename)
        {
            string connStr = "Server =.;User Id=sa;Password=*******; database = {0}";
            connStr = string.Format(connStr, dbname);
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();

            string sql = "select *from {0}";
            sql = string.Format(sql, tablename);
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter apt = new SqlDataAdapter(cmd);
            SqlCommandBuilder sb = new SqlCommandBuilder(apt);
            DataSet ds = new DataSet();
            
            try
            {
                
                apt.Fill(ds);
                Console.WriteLine("transTods_success");
            }
            catch (Exception ex)
            {
                Console.WriteLine("transTods_failed:" + ex.Message);
            }
            finally
            {
                conn.Close();
            }
            DataTable dt = ds.Tables[0];

            //int i = 0;
            //foreach(DataRow dr in ds.Tables[0].Rows)
            //{
            //    if(i>50)
            //    {
            //        ds.Tables[0].Rows.RemoveAt(i);
            //    }
            //    i++;
            //}

            int count = dt.Rows.Count;
            for(int i=0;i<count;i++)
            {
                if(i>50)
                {
                    //dt.Rows.RemoveAt(51);
                    dt.Rows[i].Delete();
                }
            }
            apt.Update(ds);           

            int j = 0;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Console.WriteLine(dr["Price"]+" "+j+" "+dr.RowState);
                j++;
            }         
        }

其他

关于数据操作在此多说一点，可以用sqldatareader进行数据读取，也可以采用insert等方法进行插入，在此采用dataset先把数据表缓存到本地表中进行操作。采用dataset包括更新数据表等都很方便，只需要用sqldataadapter进行一下update就可以。

PS：
1、在对datatable进行遍历并进行删除操作时，不要用foreach，其不允许进行增加和删除

2、如果数据表需要update，那么数据表进行增删是要用add和delete，不要用import和remove，因为在进行update时，是根据datarow的rowstate属性进行操作的。比如delete时，并不是删除row，而是把相应行的rowstate进行了修改，update时就根据此属性进行处理，本地数据表只有当采用acceptchange时才会根据rowstate属性进行数据表的相应删除。而采用import或者remove时则rowstate属性则会存在问题，比如采用remove时，整行移除后相应的rowstate也不存在，而update时，由于相应的行没有rowstate属性值，所以远程数据库不会进行删除。import倒入后rowstate状态不明，所以也无法进行远程数据库的数据更新。

总之，采用update时并不是拿本地datatable与sqlserver数据表进行对比然后更新，而是根据datatable中的datarow的rowstate属性直接修改sqlserver数据表。