SqlHelper類
作用：充當一個助人為樂的角色。這個類呢，任何類都可以調用。例如：數據的“增， 刪， 改 ，查”，數據庫的鏈接，等等。
這個類是靜態類，只要用類名.方法名（），就可以使用該方法的功能了。
//與數據交互的方法
public static class SQLHelper
{
//創建靜態方法，方便調用
//配置文件
public static string str = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
// public static string str = "data source=.;initial catalog=myschool;uid=sa;pwd=123";
//執行NonQuery命令
public static int ExecuteNonQuery(string cmdTxt, params SqlParameter[] parames)
{
return ExecuteNonQuery(cmdTxt, CommandType.Text, parames);
}
//可以使用存儲過程的ExecuteNonQuery
public static int ExecuteNonQuery(string cmdTxt, CommandType commandType, params SqlParameter[] parames)
{
//判斷腳本是否為空,直接返回0
if (string.IsNullOrEmpty(cmdTxt))
{
return 0;
}
using (SqlConnection con = new SqlConnection(str))
{
using (SqlCommand cmd = new SqlCommand(cmdTxt, con))
{
if (parames != null)
{
cmd.CommandText = cmdTxt;
cmd.Parameters.AddRange(parames);
}
con.Open();
return cmd.ExecuteNonQuery();
}
}
}
public static SqlDataReader ExecuteDataReader(string cmdTxt, params SqlParameter[] parames)
{
return ExecuteDataReader(cmdTxt, CommandType.Text, parames);
}
//SQLDataReader存儲過程方法
public static SqlDataReader ExecuteDataReader(string cmdTxt, CommandType commandType, params SqlParameter[] parames)
{
if (string.IsNullOrEmpty(cmdTxt))
{
return null;
}
SqlConnection con = new SqlConnection(str);
using (SqlCommand cmd = new SqlCommand(cmdTxt, con))
{
cmd.CommandType = commandType;
if (parames != null)
{
cmd.Parameters.AddRange(parames);
}
con.Open();
//把reader的行為加進來。當reader釋放資源的時候，con也被一塊關閉
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
}
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parames)
{
return ExecuteDataTable(sql, CommandType.Text, parames);
}
//調用存儲過程的類，關於(ExecuteDataTable)
public static DataTable ExecuteDataTable(string sql, CommandType commandType, SqlParameter[] parames)
{
if (string.IsNullOrEmpty(sql))
{
return null;
}
DataTable dt = new DataTable();
using (SqlDataAdapter da = new SqlDataAdapter(sql, str))
{
da.SelectCommand.CommandType = commandType;
if (parames != null)
{
da.SelectCommand.Parameters.AddRange(parames);
}
da.Fill(dt);
return dt;
}
}
/// <summary>
/// ExecuteScalar
/// </summary>
/// <param name="cmdTxt">第一個參數，SQLServer語句</param>
/// <param name="parames">第二個參數，傳遞0個或者多個參數</param>
/// <returns></returns>
///
public static object ExecuteScalar(string cmdTxt, params SqlParameter[] parames)
{
return ExecuteScalar(cmdTxt, CommandType.Text, parames);
}
//可使用存儲過程的ExecuteScalar
public static object ExecuteScalar(string cmdTxt, CommandType commandType, SqlParameter[] parames)
{
if (string.IsNullOrEmpty(cmdTxt))
{
return null;
}
using (SqlConnection con = new SqlConnection(str))
{
using (SqlCommand cmd = new SqlCommand(cmdTxt, con))
{
cmd.CommandType = commandType;
if (parames != null)
{
cmd.Parameters.AddRange(parames);
}
con.Open();
return cmd.ExecuteScalar();
}
}
}
//調用存儲過程的DBHelper類（關於ExeceutScalar,包含事務,只能處理Int類型，返回錯誤號）
public static object ExecuteScalar(string cmdTxt, CommandType commandType, SqlTransaction sqltran, SqlParameter[] parames)
{
if (string.IsNullOrEmpty(cmdTxt))
{
return 0;
}
using (SqlConnection con = new SqlConnection(str))
{
int sum = 0;
using (SqlCommand cmd = new SqlCommand(cmdTxt, con))
{
cmd.CommandType = commandType;
if (parames != null)
{
cmd.Parameters.AddRange(parames);
}
con.Open();
sqltran = con.BeginTransaction();
try
{
cmd.Transaction = sqltran;
sum = Convert.ToInt32(cmd.ExecuteScalar());
sqltran.Commit();
}
catch (SqlException ex)
{
sqltran.Rollback();
}
return sum;
}
}
}
}
}
MyTool類
作用：簡化代碼，提高性能，提高運行效率。
string sql = "select * from grade";
DataTable dt = SQLHelper.ExecuteDataTable(sql);
MyTool tool=new MyTool();
List<Grade> list=tool.DataTableToList<Grade>(dt);
相當於foreach循環
上面四行代碼相當於下面十行代碼：
List<Grade> list = new List<Grade>();
string sql = "select * from grade";
DataTable dt = SQLHelper.ExecuteDataTable(sql);
//將dt轉成List<Student>
foreach (DataRow row in dt.Rows)
{
//每一個row代表表中的一行 所以 一行對應一個年級對象
Grade grade = new Grade();
grade.GradeId = Convert.ToInt32(row["gradeid"]);
grade.GradeName = row["gradename"].ToString();
list.Add(grade);
}
public class MyTool
{
/// <summary>
/// DataSetToList
/// </summary>
/// <typeparam name="T">轉換類型</typeparam>
/// <param name="dataSet">數據源</param>
/// <param name="tableIndex">需要轉換表的索引</param>
/// <returns></returns>
public List<T> DataTableToList<T>(DataTable dt)
{
//確認參數有效
if (dt == null )
return null;
List<T> list = new List<T>();
for (int i = 0; i < dt.Rows.Count; i++)
{
//創建泛型對象
T _t = Activator.CreateInstance<T>();
//獲取對象所有屬性
PropertyInfo[] propertyInfo = _t.GetType().GetProperties();
for (int j = 0; j < dt.Columns.Count; j++)
{
foreach (PropertyInfo info in propertyInfo)
{
//屬性名稱和列名相同時賦值
if (dt.Columns[j].ColumnName.ToUpper().Equals(info.Name.ToUpper()))
{
if (dt.Rows[i][j] != DBNull.Value)
{
info.SetValue(_t, dt.Rows[i][j], null);
}
else
{
info.SetValue(_t, null, null);
}
break;
}
}
}
list.Add(_t);
}
return list;
}
}
}
MsgDiv類
作用：該類相當於一個控件。我們知道有這麼TextBox控件，及文本框。
但是有時候我們需要美化文字，所以就要用MsgDiv控件。而且該控件還有一個特別的作用：計時器，
就是有時間的延長，即當你運行一個窗體時，你不想馬上就要看到
該窗體，所以呢，可以用該控件設置時間 的延續，已達到效果。
首先你把這個類放項目窗體下面。
效果圖：
/// <summary>
/// 消息條回調函數委托
/// </summary>
public delegate void DGMsgDiv();
/// <summary>
/// 消息條類 帶Timer計時
/// </summary>
public class MsgDiv : System.Windows.Forms.Label
{
private Timer timerLable = new Timer();
/// <summary>
/// 消息回調 委托對象
/// </summary>
private DGMsgDiv dgCallBack = null;
#region 計時器
/// <summary>
/// 計時器
/// </summary>
public Timer TimerMsg
{
get { return timerLable; }
set { timerLable = value; }
}
#endregion
#region MsgDiv構造函數
/// <summary>
/// MsgDiv構造函數
/// </summary>
public MsgDiv()
{
InitallMsgDiv(7, 7);
}
/// <summary>
/// MsgDiv構造函數
/// </summary>
/// <param name="x">定位x軸坐標</param>
/// <param name="y">定位y軸坐標</param>
public MsgDiv(int x, int y)
{
InitallMsgDiv(x, y);
}
#endregion
#region 初始化消息條
/// <summary>
/// 初始化消息條
/// </summary>
private void InitallMsgDiv(int x, int y)
{
this.AutoSize = true;
this.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(255)))), ((int)(((byte)(255)))), ((int)(((byte)(192)))));
this.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
//this.ContextMenuStrip = this.cmsList;
this.Font = new System.Drawing.Font("宋體", 11F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
this.ForeColor = System.Drawing.Color.Red;
this.Location = new System.Drawing.Point(x, y);
this.MaximumSize = new System.Drawing.Size(980, 525);
this.Name = "msgDIV";
this.Padding = new System.Windows.Forms.Padding(7);
this.Size = new System.Drawing.Size(71, 31);
this.TabIndex = 1;
this.Text = "消息條";
this.Visible = false;
//給委托添加事件
this.DoubleClick += new System.EventHandler(this.msgDIV_DoubleClick);
this.MouseLeave += new System.EventHandler(this.msgDIV_MouseLeave);
this.MouseHover += new System.EventHandler(this.msgDIV_MouseHover);
this.timerLable.Interval = 1000;
this.timerLable.Tick += new System.EventHandler(this.timerLable_Tick);
}
#endregion
#region 將消息條添加到指定容器上
/// <summary>
/// 將消息條添加到指定容器上Form
/// </summary>
/// <param name="form"></param>
public void AddToControl(Form form)
{
form.Controls.Add(this);
}
/// <summary>
/// 將消息條添加到指定容器上GroupBox
/// </summary>
/// <param name="form"></param>
public void AddToControl(GroupBox groupBox)
{
groupBox.Controls.Add(this);
}
/// <summary>
/// 將消息條添加到指定容器上Panel
/// </summary>
/// <param name="form"></param>
public void AddToControl(Panel panel)
{
panel.Controls.Add(this);
}
#endregion
//---------------------------------------------------------------------------
#region 消息顯示 的相關參數們 hiddenClick，countNumber，constCountNumber
/// <summary>
/// 當前顯示了多久的秒鐘數
/// </summary>
int hiddenClick = 0;
/// <summary>
/// 要顯示多久的秒鐘數 可變參數
/// </summary>
int countNumber = 3;
/// <summary>
/// 要顯示多久的秒鐘數 固定參數
/// </summary>
int constCountNumber = 3;
#endregion
#region 計時器 顯示countNumber秒鐘後自動隱藏div -timerLable_Tick(object sender, EventArgs e)
private void timerLable_Tick(object sender, EventArgs e)
{
if (hiddenClick > countNumber - 2)
{
MsgDivHidden();
}
else
{
hiddenClick++;
//RemainCount();
}
}
#endregion
#region 隱藏消息框 並停止計時 +void MsgDivHidden()
/// <summary>
/// 隱藏消息框 並停止計時
/// </summary>
public void MsgDivHidden()
{
this.Text = "";
this.Visible = false;
this.hiddenClick = 0;
//this.tslblRemainSecond.Text = "";
if (this.timerLable.Enabled == true)
this.timerLable.Stop();
//調用 委托 然後清空委托
if (dgCallBack != null && dgCallBack.GetInvocationList().Length > 0)
{
dgCallBack();
dgCallBack -= dgCallBack;
}
}
#endregion
#region 在消息框中顯示消息字符串 +void MsgDivShow(string msg)
/// <summary>
/// 在消息框中顯示消息字符串
/// </summary>
/// <param name="msg">要顯示的字符串</param>
public void MsgDivShow(string msg)
{
this.Text = msg;
this.Visible = true;
this.countNumber = constCountNumber;//默認設置顯示秒數為10;
this.hiddenClick = 0;//重置倒數描述
this.timerLable.Start();
}
#endregion
#region 在消息框中顯示消息字符串 並在消息消失時 調用回調函數 +void MsgDivShow(string msg, DGMsgDiv callback)
/// <summary>
/// 在消息框中顯示消息字符串 並在消息消失時 調用回調函數
/// </summary>
/// <param name="msg">要顯示的字符串</param>
/// <param name="callback">回調函數</param>
public void MsgDivShow(string msg, DGMsgDiv callback)
{
MsgDivShow(msg);
dgCallBack = callback;
}
#endregion
#region 在消息框中顯示消息字符串 並在指定時間消息消失時 調用回調函數 +void MsgDivShow(string msg, int seconds, DGMsgDiv callback)
/// <summary>
/// 在消息框中顯示消息字符串 並在消息消失時 調用回調函數
/// </summary>
/// <param name="msg">要顯示的字符串</param>
/// <param name="seconds">消息顯示時間</param>
/// <param name="callback">回調函數</param>
public void MsgDivShow(string msg, int seconds, DGMsgDiv callback)
{
MsgDivShow(msg, seconds);
dgCallBack = callback;
}
#endregion
#region 在消息框中顯示消息字符串，並指定消息框顯示秒數 +void MsgDivShow(string msg, int seconds)
/// <summary>
/// 在消息框中顯示消息字符串，並指定消息框顯示秒數
/// </summary>
/// <param name="msg">要顯示的字符串</param>
/// <param name="seconds">消息框顯示秒數</param>
public void MsgDivShow(string msg, int seconds)
{
this.Text = msg;
this.Visible = true;
this.countNumber = seconds;
this.hiddenClick = 0;//重置倒數描述
this.timerLable.Start();
}
#endregion
//---------------------------------------------------------------------------
#region 事件們～～～！ msgDIV_MouseHover，msgDIV_MouseLeave，msgDIV_DoubleClick
//當鼠標停留在div上時 停止計時
private void msgDIV_MouseHover(object sender, EventArgs e)
{
if (this.timerLable.Enabled == true)
this.timerLable.Stop();
}
//當鼠標從div上移開時 繼續及時
private void msgDIV_MouseLeave(object sender, EventArgs e)
{
//當消息框正在顯示、回復框沒顯示、計時器正停止的時候，重新啟動計時器
if (this.Visible == true && this.timerLable.Enabled == false)
this.timerLable.Start();
}
//雙擊消息框時關閉消息框
private void msgDIV_DoubleClick(object sender, EventArgs e)
{
MsgDivHidden();
}
#endregion
}
上面這三個類的代碼，雖然是老師給的代碼，但是都是經過本人認真寫出來的，目的就是強化記憶。望對大家有幫助。
謝謝！
