C# SQL Server 操作笔记

首先目前我接触的语言连接SQL Server都需要提供一个连接字符串，所以做过的都知道怎么弄，实际上我大部分时候记不住，大概的格式为

            var str =
                "Provider=SQLOLEDB.1;" +
                "Password=" + pw + ";" +
               " Persist Security Info=True;" +
               "User ID=" + userid + ";" +
               "Initial Catalog=" + dbname + ";" +
               "Data Source=" + ip + "," + port + ";" +
               "Connection Timeout = " + timeOutValue + ";";

其实还有很多选项，不过C#的强大开始体现了，那就是  SqlConnectionStringBuilder

            var sqlsb = new SqlConnectionStringBuilder();
            sqlsb.DataSource = "10.0.0.233,1433";
            sqlsb.InitialCatalog = "Test";

            sqlsb.UserID = "test";
            sqlsb.Password = "asdf1234";
            sqlsb.IntegratedSecurity = false;

            sqlsb.Encrypt = true;
            sqlsb.ConnectTimeout = 10;

            sqlsb.TrustServerCertificate = true;//如果连接遇到证书问题，设置一直信任就好

            sqlstr = sqlsb.ToString();

然后是连接

        public void Connect(Action<SqlConnection> action, Action<string> error) {

            using (SqlConnection connection = new SqlConnection(sqlstr)) {
                try {
                    connection.Open();
                    action(connection);
                } catch (Exception e) {
                    error("数据库异常");
                    logger.Error("DB Connect:" + e.Message);
                }
            }
        }

然后是执行命令

        public void ReadAccount(string account, Action<UserLogonData> result, Action<string> error) {
            Connect(sqlcon=> {
                var cmd = sqlcon.CreateCommand();
                cmd.CommandText = @"SELECT     Password, Name
                                FROM         Accounts
                                WHERE     (Account = @account)";
                try {
                    cmd.Parameters.Add(new SqlParameter("account", account));
                    var reader = cmd.ExecuteReader();
                    if (reader.Read()) {
                        var data = new UserLogonData();
                        data.password = reader.GetString(0);
                        data.name = reader.GetString(1);
                        result(data);
                    } else
                        result(null);
                } catch(Exception e) {
                    error("数据库异常");
                    logger.Error("DB Connect:" + e.Message);
                }
              
            }, error);
        }