C# 连接SQLSERVER数据库常用操作类
//数据库连接字符串
public static string connectStr = @"server=.;database=test;uid=sa;pwd=123456;";
/// <summary>
/// 增删改操作类
/// </summary>
/// <param name="sql">执行的sql</param>
/// <returns></returns>
public static int ExecuteSql(string sql)
{
try
{
using (SqlConnection con = new SqlConnection(connectStr))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(sql, con))
{
return cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 查询sql
/// </summary>
/// <param name="sql">查询语句sql</param>
/// <returns></returns>
public static DataSet Query(string sql)
{
DataSet ds = new DataSet();
try
{
using (SqlConnection con = new SqlConnection(connectStr))
{
con.Open();
using (SqlDataAdapter cmd = new SqlDataAdapter(sql, con))
{
cmd.Fill(ds);
return ds;
}
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 执行增删改查存储过程
/// </summary>
/// <param name="ProcName">存储过程名</param>
/// <param name="parameter">存储过程参数</param>
/// <returns></returns>
public static int ExecuteProc(string ProcName, SqlParameter[] parameter)
{
try
{
using (SqlConnection con = new SqlConnection(connectStr))
{
con.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = ProcName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
for (int i = 0; i < parameter.Length; i++)
{
cmd.Parameters.Add(parameter[i]);
}
return cmd.ExecuteNonQuery();
}
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 执行查询的存储过程
/// </summary>
/// <param name="ProcName">存储过程名</param>
/// <param name="parameter">存储过程参数</param>
/// <returns></returns>
public static DataSet ExecuteProcQuery(string ProcName, SqlParameter[] parameter)
{
DataSet ds = new DataSet();
try
{
using (SqlConnection con = new SqlConnection(connectStr))
{
con.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = ProcName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
for (int i = 0; i < parameter.Length; i++)
{
cmd.Parameters.Add(parameter[i]);
}
SqlDataAdapter dt = new SqlDataAdapter(cmd);
dt.Fill(ds);
return ds;
}
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 执行事务
/// </summary>
/// <param name="sqls">执行的所有sql语句</param>
/// <returns></returns>
public static int ExecuteTranction(string sqls)
{
try
{
using (SqlConnection con = new SqlConnection(connectStr))
{
con.Open();
using (SqlTransaction transaction = con.BeginTransaction())
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.CommandText = sqls;
cmd.Connection = con;
cmd.Transaction = transaction;
int i = cmd.ExecuteNonQuery();
transaction.Commit();
return i;
}
catch (Exception)
{
transaction.Rollback();
return 0;
}
}
}
}
}
catch (Exception)
{
return 0;
}
}
最新文章
- Spring AOP：面向切面编程，AspectJ，是基于注解的方法
- LeetCode----3 Sum
- 9. Palindrome Number
- SecureCRT相关设置
- [改善Java代码]优先使用整型池
- NASA关于如何写出安全代码的10条军规
- 【linux】内核编译
- poj 2299 逆序数
- 关于一些常用的linux命令
- 关系型数据库工作原理-SQL重写(翻译自Coding-Geek文章)
- codeforces 985E Pencils and Boxes
- Express中间件，看这篇文章就够了(#^.^#)
- [转帖] 常见的cmd命令
- HTML5 监听移动端浏览器返回键兼容版本
- Checkpoint not complete
- Asp.net mvc Kendo UI Grid的使用(三)
- 使用CoreData [2]
- hdu6438 Buy and Resell
- android studio 怎么做屏幕适配?
- mysql - json串新增字段