?? sqlhelper.cs
字號:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Xml;
namespace corInfMan.Utility
{
/// <summary>
/// The SqlHelper class is intended to encapsulate high performance,
/// scalable best practices for common uses of SqlClient.
/// </summary>
public abstract class SQLHelper
{
/* *********************************數(shù)據(jù)庫連接字符串*******************************************
* SQLServer 2005:
* Data Source=192.168.1.110;Initial Catalog=mydoc;Integrated Security=True
* Data Source=192.168.1.110;Initial Catalog=mydoc;Integrated Security=True
* Access:
* Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\MyDev\myArticle\myarticle.mdb;Persist Security Info=True;Jet OLEDB:Database Password=dlxdlx
* SQLServer 2000:
* server=127.0.0.1;uid=sa;pwd=sql;database=dlx
* *******************************************************************************************/
//reading connection string from app.config file in VS2008
//public static string connectionString = Properties.Settings.Default.ConnectionString;
const string conStr = "Data Source=(local);Initial Catalog=dlxcorInfMan;Persist Security Info=True;User ID=sa;Password=sql";
public static string connectionString = conStr;
# region 獲取指定表號SingleID:GetMaxID(字段名，表名)
/// <summary>
/// 重載方法GetMaxID給定表名、字段名獲得最大值
/// </summary>
/// <param name="FieldName">字段名</param>
/// <param name="TableName">表名</param>
/// <returns></returns>
public static int GetMaxID(string FieldName, string TableName)
{
#region 下面代碼適應(yīng)與Access等不能支持存儲過程的數(shù)據(jù)庫
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = GetSingle(strsql);
if (obj == null)
{
return 1;
}
else
{
return int.Parse(obj.ToString());
}
#endregion
}
#endregion
#region 公用方法
public static bool Exists(string strSql, params SqlParameter[] cmdParms)
{
object obj = GetSingle(strSql, cmdParms);
//int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
//cmdresult = 0;
return false;
}
else
{
//cmdresult = 1;
return true;
}
//if (cmdresult == 0)
//{
// return false;
//}
//else
//{
// return true;
//}
}
#endregion
#region 執(zhí)行簡單SQL語句
/// <summary>
/// 執(zhí)行SQL語句，返回影響的記錄數(shù)
/// </summary>
/// <param name="SQLString">SQL語句</param>
/// <returns>影響的記錄數(shù)</returns>
public static int ExecuteSql(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString,connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
connection.Close();
throw new Exception(E.Message);
}
}
}
}
/// <summary>
/// 執(zhí)行帶一個(gè)存儲過程參數(shù)的的SQL語句。
/// </summary>
/// <param name="SQLString">SQL語句</param>
/// <param name="content">參數(shù)內(nèi)容,比如一個(gè)字段是格式復(fù)雜的文章，有特殊符號，可以通過這個(gè)方式添加</param>
/// <returns>影響的記錄數(shù)</returns>
public static int ExecuteSql(string SQLString,string content)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(SQLString,connection);
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( "@content", SqlDbType.NText);
myParameter.Value = content ;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows=cmd.ExecuteNonQuery();
return rows;
}
catch(System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 向數(shù)據(jù)庫里插入圖像格式的字段(和上面情況類似的另一種實(shí)例)
/// </summary>
/// <param name="strSQL">SQL語句</param>
/// <param name="fs">圖像字節(jié),數(shù)據(jù)庫的字段類型為image的情況</param>
/// <returns>影響的記錄數(shù)</returns>
public static int ExecuteSqlInsertImg(string strSQL,byte[] fs)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(strSQL,connection);
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( "@fs", SqlDbType.Image);
myParameter.Value = fs ;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows=cmd.ExecuteNonQuery();
return rows;
}
catch(System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 執(zhí)行一條計(jì)算查詢結(jié)果語句，返回查詢結(jié)果（object）。
/// </summary>
/// <param name="SQLString">計(jì)算查詢結(jié)果語句</param>
/// <returns>查詢結(jié)果（object）</returns>
public static object GetSingle(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using(SqlCommand cmd = new SqlCommand(SQLString,connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch(System.Data.SqlClient.SqlException e)
{
connection.Close();
throw new Exception(e.Message);
}
}
}
}
/// <summary>
/// 執(zhí)行查詢語句，返回SqlDataReader
/// </summary>
/// <param name="strSQL">查詢語句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string strSQL)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(strSQL,connection);
try
{
connection.Open();
SqlDataReader myReader = cmd.ExecuteReader();
return myReader;
}
catch(System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// 執(zhí)行查詢語句，返回DataSet
/// </summary>
/// <param name="SQLString">查詢語句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString,connection);
command.Fill(ds,"ds");
}
catch(System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
//return null;
}
return ds;
}
}
/// <summary>
/// 執(zhí)行查詢語句，返回DataSet-分頁顯示
/// </summary>
/// <param name="SQLString">查詢語句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString,int startpage,int pagesize)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -