C#连接数据库

第一步先在配置文件中写入数据库的配置信息

<add name="connString" connectionString="server=服务器名;database=数据库名;uid=登录名;pwd=登录密码"/>

    <connectionStrings>
        <add name="connString" connectionString="server=LIN;database=TestDB;uid=sa;pwd=123456"/>
    </connectionStrings>

读取配置文件

private static string connectionString= ConfigurationManager.ConnectionStrings["connString"].ToString();

这里要引用类Configuration

SqlConnection conn = new SqlConnection(connectionString);

写sql

string sql = $"insert into test(Tname,age,datetime)values('{name}','{age}','{time}')";

            SqlCommand cmd = new SqlCommand(cmdTest, conn);
            conn.Open();
            int cound=cmd.ExecuteNonQuery();

count，返回一个值，1为有一行受影响

查询：

        static void SelectDB()
        {
            string sql = $"select * from SalesLT.Customer where CustomerID=5";
            SqlDataReader count =SQLHepler.ExecuteScalar(sql);//执行查询结果第一行
            while(count.Read())//查询结果返回多行时，循环输出
            {
                Console.WriteLine(count["LastName"] + "\t" + count["FirstName"]);
            }
            
        }
        static void SelectDB2() {
            string sql = $"select * from SalesLT.Customer where CustomerID=5 or CustomerID=7";
            sql += "select * from SalesLT.Address a inner join SalesLT.CustomerAddress ca\r\non a.AddressID=ca.AddressID\r\ninner join SalesLT.Customer c\r\non c.CustomerID=ca.CustomerID\r\nwhere a.City='Bothell'";
            SqlDataReader count = SQLHepler.ExecuteScalar(sql);
            while (count.Read())
            {
                Console.WriteLine(count["LastName"] + "\t" + count["FirstName"]);
            }
            if (count.NextResult())//当一次查询多个表时，从第一个开始
            {
                while (count.Read())
                {
                    Console.WriteLine(count["LastName"] + "\t" + count["FirstName"]);
                }
            }
        }

 删除：（增删改都是这个类型）

        static void DeleteDB()
        {
            string id = "32";
            string sql2 = string.Format("delete from test where id={0}",id);
            SqlConnection conn = new SqlConnection(connectionSteing);
            SqlCommand cmd= new SqlCommand(sql2,conn);
            conn.Open();
            int cound=cmd.ExecuteNonQuery();
            conn.Close();
            Console.WriteLine(cound);
        }

工具类：

操作：

        public static int ExecuteNonQuery(string cmdTest)
        {
            SqlConnection conn = new SqlConnection(connectionSteing);
            SqlCommand cmd = new SqlCommand(cmdTest, conn);
            try {
                conn.Open();
                int cound = cmd.ExecuteNonQuery();
                return cound;
            }
            catch(Exception ex) {
                string msg = $"[{DateTime.Now.ToLongTimeString()}]执行方法：public ststic int ExecuteNonQuery(string cmdTest)发生异常";
                msg+=ex.Message;
                throw new Exception(msg);
            }finally {//无论如何都会实现
                conn.Close();
            }


            
            
        }

查询单表：

        public static object ExecuteScalar(string cmdTest)
        {
            SqlConnection conn = new SqlConnection(connectionSteing);
            SqlCommand cmd = new SqlCommand(cmdTest, conn);
            try {
                conn.Open();
                object cound = cmd.ExecuteScalar();
                return cound;
            }
            catch(Exception ex) {
                string msg = $"[{DateTime.Now.ToLongTimeString()}]执行方法：public ststic int ExecuteNonQuery(string cmdTest)发生异常";
                msg+=ex.Message;
                throw new Exception(msg);
            }finally {
                conn.Close();
            }
        }

查询多表：

 

        public static SqlDataReader ExecuteScalar(string cmdTest)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(cmdTest,conn);
            conn.Open();
            SqlDataReader result = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            //conn.Close();
            return result;
        }