<pre><code>
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
namespace demo_DevExpress_
{
public class SqlHelp
{
/// <summary>
/// 獲取連接數(shù)據(jù)庫字符串
/// </summary>
// private static string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
private static string connStr = "Data Source=.;Initial Catalog=text;Integrated Security=True";
/// <summary>
/// 對(duì)連接執(zhí)行 Transact-SQL 語句并返回受影響的行數(shù)
/// </summary>
/// <param name="sql">對(duì)數(shù)據(jù)庫表進(jìn)行Insert畏浆，Update和Delete語句</param>
/// <param name="parameters">sql語句參數(shù)</param>
/// <returns>返回值為該命令所影響的行數(shù)</returns>
public static int ExecuteNonQuery(CommandType cmdTye, string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = cmdTye;
cmd.Parameters.AddRange(parameters);
int i = cmd.ExecuteNonQuery();
return i;
}
}
}
/// <summary>
/// 執(zhí)行查詢，并返回查詢所返回的結(jié)果集中第一行的第一列盈罐。一般用于SQL聚合函數(shù)逻恐，如：count,Max剿配，min恃逻，avg枪狂。桂躏。
/// </summary>
/// <param name="sql">要執(zhí)行的sql語句</param>
/// <param name="parameters">sql語句的參數(shù)</param>
/// <returns>返回查詢所返回的結(jié)果集中第一行的第一列碳想。忽略其他列或行烧董。返回的最大字符數(shù)為 2033 個(gè)字符。</returns>
public static object ExecuteScalar(CommandType cmdTye, string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = cmdTye;
cmd.Parameters.AddRange(parameters);
//ExcuteScalar()的返回值是object類型的（如果想返回int類型的數(shù)據(jù)胧奔，可先將cmd.ExecuteSalar()轉(zhuǎn)化成int類型再進(jìn)行返回）
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// DataSet是將查詢結(jié)果填充到本地內(nèi)存中逊移，這樣即使與連接斷開，服務(wù)器的連接斷開龙填，都不會(huì)影響數(shù)據(jù)的讀取胳泉。但是它也有一個(gè)壞處，就是只有小數(shù)據(jù)量才能往里面存放岩遗，數(shù)據(jù)量大了就給你的本地內(nèi)存沖爆了扇商。電腦會(huì)卡死去。大數(shù)據(jù)量的話還得用SqlDataReader
/// </summary>
/// <param name="sql">要執(zhí)行的sql語句</param>
/// <param name="parameters">sql語句的參數(shù)</param>
/// <returns>返回值是一個(gè)DataSet</returns>
public static DataSet ExecuteDataSet(CommandType cmdTye, string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = cmdTye;
cmd.Parameters.AddRange(parameters);
DataSet ds = new DataSet();
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
//將cmd的執(zhí)行結(jié)果填充到ds里面
adapter.Fill(ds);
return ds;
}
}
}
}
/// <summary>
/// 執(zhí)行查詢宿礁，并將查詢的結(jié)果以DataTable的形式返回
/// </summary>
/// <param name="sql">要執(zhí)行的sql語句</param>
/// <param name="parameters">sql語句的參數(shù)</param>
/// <returns>返回值是一個(gè)DataTable</returns>
public static DataTable ExecuteDataTable(CommandType cmdTye, string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
//換一種寫法案铺，與上面的幾種寫法稍稍有點(diǎn)不同
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandText = sql;
cmd.CommandType = cmdTye;
cmd.Parameters.AddRange(parameters);
DataTable dt = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
adapter.Fill(dt);
return dt;
}
}
}
}
/// <summary>
/// 將查詢的結(jié)果轉(zhuǎn)換成一個(gè)T類型的對(duì)象
/// </summary>
/// <typeparam name="T">泛型類T</typeparam>
/// <param name="sql">要執(zhí)行的sql語句</param>
/// <param name="parameters">sql語句的參數(shù)</param>
/// <returns>T類型的對(duì)象集合</returns>
public static List<T> ExecuteClass<T>(CommandType cmdTye, string sql, params SqlParameter[] parameters)
{
List<T> list = new List<T>();
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = cmdTye;
cmd.Parameters.AddRange(parameters);
DataSet ds = new DataSet();
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
adapter.Fill(ds);
}
//獲取ds里面的第0個(gè)表（索引從0開始）
DataTable dt = ds.Tables[0];
//獲取T類型的所有公有屬性
PropertyInfo[] tMembers = typeof(T).GetProperties();
//遍歷dt表的所有行
for (int i = 0; i < dt.Rows.Count; i++)
{
//創(chuàng)建一個(gè)泛型T類型的對(duì)象t。
T t = Activator.CreateInstance<T>();
//遍歷dt表的所有列
for (int j = 0; j < dt.Columns.Count; j++)
{
//遍歷T類型的所有公有屬性
foreach (PropertyInfo tMember in tMembers)
{
//如果發(fā)現(xiàn)dt表中的列名與遍歷出來的T類型屬性名相同
if (dt.Columns[j].ColumnName.ToUpper().Equals(tMember.Name.ToUpper()))
{
//將dt.Rows[i][j]賦值給t對(duì)象的tMember成員
if (dt.Rows[i][j] != DBNull.Value)
{
tMember.SetValue(t, dt.Rows[i][j], null);
}
else
{
tMember.SetValue(t, null, null);
}
break;//;//注意這里的break是寫在if語句里面的梆靖，意思就是說如果列名和屬性名稱相同并且已經(jīng)賦值了控汉，那么我就跳出foreach循環(huán)笔诵，進(jìn)行j+1的下次循環(huán)
}
}
}
list.Add(t);
}
return list;
}
}
}
/// <summary>
/// 直接將查詢到的數(shù)據(jù)（表）轉(zhuǎn)換成一個(gè)泛型類
/// </summary>
/// <typeparam name="T">泛型類T</typeparam>
/// <param name="cmdText">要執(zhí)行的sql語句</param>
/// <param name="parameters">sql語句的參數(shù)</param>
/// <returns>T類型的對(duì)象的集合</returns>
///
/*
public static List<T> SelectDataToList<T>(string cmdText, params SqlParameter[] parameters)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
adapter.Fill(dt);
}
}
}
IList<T> list = new List<T>(); //實(shí)例化一個(gè)泛型類
var tMembers = typeof(T).GetProperties(); //獲取T類的所有公共屬性
for (int i = 0; i < dt.Rows.Count; i++) //遍歷dt表的行
{
T t = Activator.CreateInstance<T>();//創(chuàng)建T類型的實(shí)例
for (int j = 0; j < dt.Columns.Count; j++) //遍歷dt表的列
{
foreach (var tMember in tMembers) //遍歷T類的公共方法
{
if (tMember.Name.ToUpper() == dt.Columns[j].ColumnName.ToUpper()) //如果屬性的名字與列的名字一致，則賦值
{
if (dt.Rows[i][j] != null)
{
tMember.SetValue(t, dt.Rows[i][j], null);
}
else
{
tMember.SetValue(t, null, null);
}
break;
}
}
}
list.Add(t);
}
return list.ToList();
}
*/
/*
public static object ExecuteDataReader(string sql, params SqlParameter[] paramters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using(SqlCommand cmd=new SqlCommand(sql,conn))
{
cmd.Parameters.AddRange(paramters);
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read()==true)
{
}
}
}
}
}
*/
/// <summary>
/// 從數(shù)據(jù)庫取值賦給.net類的對(duì)象屬性姑子，如果值為DBNull.Value,則將值轉(zhuǎn)換成.Net認(rèn)的null
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public static object FromDbValue(object value)
{
if (value == DBNull.Value)
{
return null;
}
else
{
return value;
}
}
/// <summary>
/// 將.net類對(duì)象的屬性值插入到數(shù)據(jù)庫嗤放，如果類的屬性值為null，則將值轉(zhuǎn)換成數(shù)據(jù)庫認(rèn)的DBNull.value
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public static object ToDbValue(object value)
{
if (value == null)
{
return DBNull.Value;
}
else
{
return value;
}
}
}
}
</code></pre>
Here is an example of AppleScript: tell application "Foo" beep end tell
