C#连接数据库

lin-07 / 2023-05-05 / 原文

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

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

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