C# WinForm 数据库连接及对数据库的相关操作
2024-09-06 15:28:21
1.首先在App.config配置文件中配置数据库连接字符串：
<appSettings>
<add key="connectionstring" value="server=XXX.XX.X.XX;uid=sa;pwd=******;database=XXXX"/>
<!--<add key="datahost" value="XXX.XX.X.XX" />
<add key="database" value="XXXX" />
<add key="uid" value="sa" />
<add key="pwd" value="******" />-->
</appSettings>
2.代码获取配置文件中设置的数据库连接字符串
private static string connectionString = ConfigurationManager.AppSettings["connectionstring"];
3.创建数据库连接方法：
public static SqlConnection CreateSqlConn() {
SqlConnection conn = new SqlConnection(connectionString);
return conn;
}
4.通过创建数据库操作类（SqlCommand），实现对数据库的增删改查操作：
增：
public static int SaveWorkInfo(SqlConnection conn,string workName,string dealPeriod,string syncType) {
if(conn.State == ConnectionState.Closed){
conn.Open();
}
IDbTransaction tran = conn.BeginTransaction();
int id = 0;
try {
string tableName = "HH_SyncWorkInfo";
string column = "WorkName,DealPeriod,SyncType,CreatedTime";
string value = "'" + workName + "','" + dealPeriod + "','" + syncType + "',getDate()";
id = createPkReturnId(tableName,column,value,conn,tran);
if(id > 0){
tran.Commit();
conn.Close();
}
}catch(Exception ex){
tran.Rollback();
conn.Close();
throw new Exception("保存数据失败！具体详情：" + ex.Message.ToString());
}
return id;
}
private static int createPkReturnId(string tableName, string column, string value, SqlConnection conn, IDbTransaction tran)
{
try
{
String strSQL = "INSERT INTO " + tableName + " (" + column + ") VALUES (" + value + ");SELECT @@Identity";
SqlCommand cmd = (SqlCommand)conn.CreateCommand();
cmd.CommandText = strSQL;
cmd.Transaction = (SqlTransaction)tran;
int id = Int32.Parse(cmd.ExecuteScalar().ToString());
return id;
}
catch (Exception ex)
{
conn.Close();
throw new Exception("主表数据保存失败！具体详情：" + ex.Message.ToString());
}
}
删：
public static bool DeleteWorkItem(string ID)
{
SqlConnection conn = CreateSqlConn();
conn.Open();
IDbTransaction tran = conn.BeginTransaction();
try
{
string strSQL = "delete HH_SyncWorkInfo where ID='" + ID + "'";
int result = conn.Execute(strSQL, null, tran);
if (result > 0)
{
tran.Commit();
conn.Close();
return true;
}
else {
tran.Rollback();
conn.Close();
return false;
}
}
catch (Exception ex)
{
tran.Rollback();
conn.Close();
throw new Exception("删除失败！具体详情：" + ex.Message.ToString());
}
}
改：
public static bool UpdateWorkInfo(string id, string workName, string dealPeriod, string syncType)
{
using (var conn = CreateSqlConn())
{
string sql_update = "update HH_SyncWorkInfo SET WorkName = '" + workName + "',dealPeriod = '"+ dealPeriod + "',syncType= '"+ syncType +"' where ID = " + id;
int result = conn.Execute(sql_update); if (result > 0)
return true;
else
return false;
}
}
查：
public static List<WorkInfo> GetWorksInfo()
{
using (var conn = CreateSqlConn())
{
string sql = "select * from HH_SyncWorkInfo";
List<WorkInfo> list = conn.Query<WorkInfo>(sql).AsList();
if (list.Count > 0)
{
return list;
}
else
{
return null;
}
}
}
5.调用存储过程
/// <summary>
///
/// </summary>
/// <param name="sql">存储过程名称</param>
/// <param name="parameters">参数数组</param>
/// <returns></returns>
public static string exectueProcude(string sql, params SqlParameter[] parameters) {
using (SqlConnection conn = SqlHelperDB.CreateSqlConn())
{
conn.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parameters);
string result = cmd.ExecuteScalar().ToString();
cmd.Parameters.Clear();
conn.Close();
return result;
}
}
}
通过ExecuteScalar()返回执行结果第一行第一列的值。
在程序中调用执行存储过程方法：
SqlParameter[] para = new SqlParameter[1];
para[0] = new SqlParameter("@OrgID", org_list[nRow].FID);
string Max_CreateTime = ProviderDB.executeProcude("HH_P_GET_GetMax_CreateTime", para);
最新文章
- [LeetCode_5] Longest Palindromic Substring
- WebApi多数据库切换
- linux 查看系统版本
- MongoDB 3.0.6的主,从,仲裁节点搭建
- javaScript-原型、继承-01
- Android下HelloWorld项目的R.java文件介绍
- InnoDB关键特性之doublewrite
- java笔记5之逻辑运算符以及&&与&的区别
- c# 异步调用简单例子（转载）
- Kattis -Bus Numbers
- 你不得不了解的应用容器引擎---Docker
- select联动遇到的问题
- [Swift]LeetCode87. 扰乱字符串 | Scramble String
- package-lock.json和package.json区别
- ActiveQt框架 禁止弹出ActiveX控件交互提示
- 使用maven profile指定配置文件打包适用多环境
- POJ 1063 - Flip and Shift
- Informix存储过程
- 使用maven创建工程报错Could not resolve archetype org.apache.maven.archetype
- maven 引入jar包