重复SQL连接，我应该创建一个类
本文关键字：一个 创建 SQL 连接 我应该 重复 | 更新日期: 2023-09-27 18:08:21
我对c#还是很陌生，我在一个页面上有多个计数查询。我不需要在其他任何地方使用这些查询，因此避免了创建类。尽管如此，我还是认为一定有一个更有效的方法，但我想知道那将是什么!
这里有一个例子
private void cntUp() {
Dictionary<string, string> crd = getCredentials(Session["secure"].ToString());
Label UserUpcoming = frmDash.FindControl("lblUserReviewUp") as Label;
using (SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["CS"].ConnectionString))
{
con.Open();
try
{
using (SqlCommand countUpcoming = new SqlCommand("SELECT COUNT(*) FROM vw_dash_user_upcoming WHERE Employee_ID = @employee_id", con))
{
countUpcoming.Parameters.Add(new SqlParameter("employee_id", crd["employee_id"].ToString()));
SqlDataReader readerUpcoming = countUpcoming.ExecuteReader();
while (readerUpcoming.Read())
{
UserUpcoming.Text = readerUpcoming.GetInt32(0).ToString();
}
}
con.Close();
}
catch
{
Response.Redirect(this.ErrorPage);
}
}
}
下面的Repository适用于SQL Server。
基本上，您可以发出常规查询或参数化查询。您可以将参数作为所有字符串传递，或者如果要使用具有强类型数字和日期的模型，则可以将参数作为对象传递。
如果您在开发中不使用这样的结构，您可以去掉发布模式信息，这只是为了使在开发和生产数据库之间切换更容易，而无需编码更改。
一个例子是:
var updateStatement = "UPDATE OCCHistoryHeaders SET ValidatingUsername=@pUsername,ValidatingWorkstation=@pWorkstation,CurrentState = @pCurrentStatus,RejectionReason = @pRejectionReason, AutomatedValidation = '0' WHERE BatchId = @pBatchId";
var parameters = new Dictionary<string, object>
{
{"pUsername", Environment.UserName},
{"pWorkstation", Environment.MachineName},
{"pCurrentStatus", currentStatus},
{"pRejectionReason", rejectionReason},
{"pBatchId", batchId}
};
var absRowsUpdated = _occDb.ExecuteParameterizedNonQueryObjects(updateStatement, parameters);
这是类…
public class SomeRepository
{
private string _connectionString { get; set; }
private SqlConnection _sqlConnection { get; set; }
public SomeRepository()
{
switch (AppSettings.ReleaseMode)
{
case ReleaseMode.DEV:
_connectionString = "server=;database=;User Id=;Password=";
break;
case ReleaseMode.PRODUCTION:
_connectionString = "server=;database=;User Id=;Password=";
break;
}
}
public DataTable ExecuteQuery(string commandText)
{
var dataTable = new DataTable();
var _sqlConnection = new SqlConnection(_connectionString);
var cmd = new SqlCommand(commandText, _sqlConnection);
var da = new SqlDataAdapter(cmd);
try
{
_sqlConnection.Open();
da.Fill(dataTable);
}
catch (Exception ex)
{
var errorText = string.Format("Occ Repository ExecuteQuery Error : QueryString={0} :: Error={1}", commandText, ex.Message);
throw new Exception(errorText, ex);
}
finally
{
da.Dispose();
_sqlConnection.Dispose();
}
return dataTable;
}
public DataTable ExecuteParameterizedQuery(string commandText, Dictionary<string, string> parameters)
{
var dataTable = new DataTable();
var _sqlConnection = new SqlConnection(_connectionString);
var cmd = new SqlCommand(commandText, _sqlConnection);
var da = new SqlDataAdapter(cmd);
foreach (var entry in parameters)
{
cmd.Parameters.Add(entry.Key, entry.Value);
}
try
{
_sqlConnection.Open();
da.Fill(dataTable);
}
catch (Exception ex)
{
var errorText = string.Format("Occ Repository ExecuteQuery Error : QueryString={0} :: Error={1}", commandText, ex.Message);
throw new Exception(errorText, ex);
}
finally
{
da.Dispose();
_sqlConnection.Dispose();
}
return dataTable;
}
public DataTable ExecuteParameterizedQueryObjects(string commandText, Dictionary<string, object> parameters)
{
var dataTable = new DataTable();
var _sqlConnection = new SqlConnection(_connectionString);
var cmd = new SqlCommand(commandText, _sqlConnection);
var da = new SqlDataAdapter(cmd);
foreach (var entry in parameters)
{
cmd.Parameters.Add(entry.Key, entry.Value);
}
try
{
_sqlConnection.Open();
da.Fill(dataTable);
}
catch (Exception ex)
{
var errorText = string.Format("Occ Repository ExecuteQuery Error : QueryString={0} :: Error={1}", commandText, ex.Message);
throw new Exception(errorText, ex);
}
finally
{
da.Dispose();
_sqlConnection.Dispose();
}
return dataTable;
}
public int ExecuteNonQuery(string commandText)
{
var _sqlConnection = new SqlConnection(_connectionString);
var rowsAffected = 0;
try
{
var cmd = new SqlCommand(commandText, _sqlConnection);
_sqlConnection.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
var errorText = string.Format("Occ Repository ExecuteNonQuery Error : Command={0} :: Error={1}", commandText, ex.Message);
throw new Exception(errorText, ex);
}
finally
{
_sqlConnection.Dispose();
}
return rowsAffected;
}
public int ExecuteParameterizedNonQuery(string commandText, Dictionary<string, string> parameters)
{
var _sqlConnection = new SqlConnection(_connectionString);
var rowsAffected = 0;
var cmd = new SqlCommand(commandText, _sqlConnection);
foreach (var entry in parameters)
{
cmd.Parameters.Add(entry.Key, entry.Value);
}
try
{
_sqlConnection.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
var errorText = string.Format("Occ Repository ExecuteNonQuery Error : Command={0} :: Error={1}", commandText, ex.Message);
throw new Exception(errorText, ex);
}
finally
{
_sqlConnection.Dispose();
}
return rowsAffected;
}
public int ExecuteParameterizedNonQueryObjects(string commandText, Dictionary<string, object> parameters)
{
var _sqlConnection = new SqlConnection(_connectionString);
var rowsAffected = 0;
var cmd = new SqlCommand(commandText, _sqlConnection);
foreach (var entry in parameters)
{
cmd.Parameters.Add(entry.Key, entry.Value);
}
try
{
_sqlConnection.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
var errorText = string.Format("Occ Repository ExecuteNonQuery Error : Command={0} :: Error={1}", commandText, ex.Message);
throw new Exception(errorText, ex);
}
finally
{
_sqlConnection.Dispose();
}
return rowsAffected;
}
}
如果你只做一个地方的东西，通常你会使所有的东西可读性差。所以我可能会避免它。这是一种常见的模式，但是，您可以为自己编写一个带有一些函数的实用程序类。
//requires query parameters to have names @0, @1 etc in string
public static List<object[]> Query(String query, params String[] parameters) //no injection check on this string, be careful.
{
using(SqlConnection conn = new SqlConnection(_CONN_STRING_))
{
conn.Open()
using(SqlCommand cmd = new SqlCommand(query, conn))
{
AddSqlParams(cmd, parameters);
return Query(cmd);
}
}
}
/// <summary>Generic SQL query. Requires open connection.</summary>
/// <param name="cmd">SqlCommand object with all necessary fields configured.</param>
/// <returns>A list of Object arrays (each array is one row).</returns>
private static List<Object[]> Query(SqlCommand cmd)
{
List<Object[]> results = new List<Object[]>();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Object[] row = new Object[rdr.VisibleFieldCount];
rdr.GetValues(row);
results.Add(row);
}
return results;
}
}
private static void AddSqlParams(SqlCommand cmd, params String[] sqlParams)
{
for (Int32 i = 0; i < sqlParams.Length; i++)
cmd.Parameters.AddWithValue("@" + i, (Object)sqlParams[i] ?? DBNull.Value);
}
然后使用like
UserUpcoming.Text = Query("SELECT COUNT(*) FROM vw_dash_user_upcoming WHERE Employee_ID = @0", crd["employee_id"].ToString())[0][0];
我喜欢为数据库中的每个表设置一个单独的类。在您的情况下，我将有一个单独的类vw_dash_user_upcoming，这将有一个静态方法类似于…
public static int CountEmployees(int employeeId) {
int returnValue;
// do database stuff here
return returnValue;
}
我通常在我的项目中创建一个名为"database"的文件夹和命名空间，所有数据库的东西都放在那里。没有数据库访问权限。对我来说，这确实有助于保持事情的条理性，尤其是在一个大项目中。
我个人会创建一个单独的类，原因很简单，因为在一个类中保留具有完全不同职责的代码片段不是一个好主意。当代码被分成小类时，代码更容易阅读和维护。
与数据库的通信应该分开，以便更容易在数据源之间切换(例如不同的数据库服务器)