using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace ADO1
{
public class DBHelper
{
public static string Message = string.Empty;
static SqlConnection conn = null;
/// <summary>
/// 連接配接資料庫
/// </summary>
private static void OpenConn()
{
conn = new SqlConnection("Data Source=.;Initial Catalog=MySchoolBase;Integrated Security=True");
conn.Open();
}
/// <summary>
/// 執行增删改操作
/// </summary>
/// <param name="sql">要執行的SQL語句</param>
/// <returns>傳回成功所影響的行數</returns>
public static int ExecuteCommand(string sql)
{
int ok = 0;
try
OpenConn();
SqlCommand cmd = new SqlCommand(sql, conn);
ok = cmd.ExecuteNonQuery();
Close();
}
catch (Exception e)
{
ok = -1;
Message = e.Message;
}
return ok;
}
/// <summary>
/// 執行查詢傳回單行單列的數值形式
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExecuteScalar(string sql)
{
int ok = 0;
try
{
OpenConn();
SqlCommand cmd = new SqlCommand(sql,conn);
ok = Convert.ToInt32(cmd.ExecuteScalar());
Close();
catch (Exception e)
{
ok = -1;
Message = e.Message;
}
return ok;
}
public static DataTable GetDataTable(string sql)//使用Adapter對象連接配接資料庫表(不傳表名)
DataTable dt = new DataTable();
try
OpenConn();
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
adapter.Fill(dt);
Close();
}
catch (Exception e)
{
Message = e.Message;
}
return dt;
}
public static DataTable GetDataTable(string sql, string tableName)//使用Adapter對象連接配接資料庫表
{
DataTable dt = new DataTable(tableName);
try
{
OpenConn();
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
adapter.Fill(dt);
Close();
}
catch (Exception e)
{
Message = e.Message;
}
return dt;
/// <summary>
/// 執行查詢
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string sql)
{
SqlDataReader rs = null;
OpenConn();
SqlCommand cmd = new SqlCommand(sql,conn);
rs = cmd.ExecuteReader();
}
catch (Exception e)
{
Message = e.Message;
}
return rs;
}
/// <summary>
/// 關閉資料庫連接配接
/// </summary>
public static void Close()
{
if (conn != null)
{
conn.Close();
}
}
}