首先目前我接触的语言连接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);
}