一个简单的数据库操作类、封装了一些简单的操作

来源：互联网发布：网络歌老情歌经典大全编辑：程序博客网时间：2024/06/03 13:12

using System;
using System.Data.SqlClient;
using System.Data;

namespace DemoConsoleApplication
{
class SQLHelper
{
private static string FLastError;
/// <summary>
/// 最近一次异常信息
/// </summary>
public static string LastError
{
get { return FLastError; }
}

/// <summary>
/// 通用异常处理函数
/// </summary>
/// <param name="e">需要处理的异常</param>
private static void HandleException(Exception e)
{
if (e is SqlException)
{
FLastError = string.Format("在打开连接时出现连接级别的错误:{0}", e.Message);
}
else if (e is InvalidOperationException)
{
FLastError = e.Message;
}
else if (e is DBConcurrencyException)
{
FLastError = string.Format("尝试执行 INSERT、UPDATE 或 DELETE 语句，但没有记录受到影响:{0}", e.Message);
}
else
{
FLastError = string.Format("未预料的异常:", e.Message);
}
}
/// <summary>
/// 获取数据库连接字符串、子类覆盖后自行修改
/// </summary>
/// <returns>连接字符串</returns>
private static string GetConnectionString()
{
return null;
}

private static SqlConnection OpenConnection()
{
return null;
}
/// <summary>
/// 无返回值的SQL语句执行
/// </summary>
/// <param name="ASql">欲执行的SQL语句</param>
/// <param name="AParams">参数集合</param>
/// <returns>影响记录的行数</returns>
public static int ExecNonSQL(string ASql, params SqlParameter[] AParams)
{
using (SqlConnection Conn = new SqlConnection(GetConnectionString()))
{
try
{
//Open异常捕获
Conn.Open();
using (SqlCommand Cmd = Conn.CreateCommand())
{
Cmd.CommandText = ASql;
foreach (SqlParameter param in AParams)
{
Cmd.Parameters.Add(param);
}
return Cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
HandleException(ex);
}
}

return -1;
}

/// <summary>
/// 获得离线数据集合
/// </summary>
/// <param name="ASql">欲执行的SQL语句</param>
/// <param name="AParams">与SQL相关的参数</param>
/// <returns>返回查询结果集合</returns>
public static DataSet ExecSQLByDataSet(string ASql, params SqlParameter[] AParams)
{
using (SqlConnection Conn = new SqlConnection(GetConnectionString()))
{
try
{
Conn.Open();
using (SqlCommand Cmd = Conn.CreateCommand())
{
Cmd.CommandText = ASql;
foreach (SqlParameter param in AParams)
{
Cmd.Parameters.Add(param);
}
SqlDataAdapter Adapter = new SqlDataAdapter(Cmd);
DataSet Result = new DataSet();
Adapter.Fill(Result);
return Result;
}
}
catch (Exception ex)
{
HandleException(ex);
}
}
return null;
}

/// <summary>
/// 将本地修改的结果集提交至服务器
/// </summary>
/// <param name="ADataSet">已修改的结果集合</param>
public static void UpdateByDataSet(DataSet ADataSet)
{
using (SqlConnection Conn = new SqlConnection(GetConnectionString()))
{
Conn.Open();
using (SqlCommand Cmd = Conn.CreateCommand())
{
SqlDataAdapter Adapter = new SqlDataAdapter(Cmd);
new SqlCommandBuilder(Adapter);
Adapter.ContinueUpdateOnError = true;
try
{
Adapter.Update(ADataSet);
}
catch (Exception ex)
{
HandleException(ex);
}
}
}
}
}

}

//-----调用

using System;
using System.Data.SqlClient;

namespace DemoConsoleApplication
{
class Program
{
//数据库连接字符串、根据实际修改
private const string ConnectionString = @"Data Source=|DataDirectory|\Database1.sdf";

static void Main(string[] args)
{
//获取用户输入的内容
Console.WriteLine("请输入用户名");
string Passport = Console.ReadLine();
Console.WriteLine("请输入密码");
string Password = Console.ReadLine();

if (1 == SQLHelper.ExecNonSQL("select * from TB_Users where passport=@UN and password=@PWD",
new SqlParameter("UN", Passport),new SqlParameter("PWD", Password)))
Console.WriteLine("登陆成功！");
else
Console.WriteLine("登陆失败！");
Console.ReadKey();//防止控制台程序一闪而过、而看不到输出结果
}
}
}

0 0