C#(winForm)寫在公用類的幾個常用方法

/***************************************************************************** 說明
** 1.擷取資料庫的串連，傳回值需判斷是否為null-----------GetSqlConnection
* 2.根據Select－－查詢語句，返回DataSet-------------------GetDataSet
* 3.使用資料庫內容填充DataGrid----------------FillDataGridFromSQLString
* 4.使用資料庫內容填充DataGrid----------------FillDataGridFromSQLString(重載)
* 5、返回SQL語句所查詢出來的行數-------------------------------GetRowCount
* 6.填充下拉式清單------------------------------------------FillComboBox*
7.由一條SQL語句產生一個DataReader；傳回值需要判斷是否為空白------GetDataReader
* 8.返回單個查詢資料：第一列，第一行的值-------------------GetFirstData
* 9.對資料庫中的一條記錄操作：增、刪、更新---------------ExecuteCommand
* 10.對資料庫進行增刪改操作-----------------------------ExecuteCommand2
* 11.判斷str是不是全是由數字構成-------------------------IsNumeric
* 12.檢測含有中文字串的實際長度------------------------len
***************************************************************************/
using System;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
namespace Tayside.Common
{
/// <summary>
/// DataBase 的摘要說明。
/// </summary>
public class DataBase
{
public DataBase()
{
}
/// <summary>
/// 1.擷取資料庫的串連，傳回值需判斷是否為null
/// </summary>
/// <returns></returns>
public static SqlConnection GetSqlConnection()
{
string strCnn = "Server=192.168.12.136;database=Tayside;user id=sa;password=";
try
{
SqlConnection sqlCnn = new SqlConnection(strCnn);
sqlCnn.Open();
return sqlCnn;
}
catch(Exception ee)
{
string temp=ee.Message;
return null;
}
}

/// <summary>
/// 擷取SqlCommand對象
/// </summary>
/// <returns></returns>
public static SqlCommand GetSqlCommand()
{
SqlConnection sqlCnn = GetSqlConnection();
if(sqlCnn == null)
return null;
else
{
SqlCommand sqlCmm = new SqlCommand();
sqlCmm.Connection = sqlCnn;
return sqlCmm;
}
}

/// <summary>
/// 2.根據Select－－查詢語句，返回DataSet
/// </summary>
/// <param name="strSql">Select SQL語句</param>
/// <returns>傳回值需判斷是否為空白</returns>
public static DataSet GetDataSet(string strSql)
{
try
{
using( SqlConnection sqlCnn = GetSqlConnection() )
{
SqlDataAdapter dataAdapter = new SqlDataAdapter( strSql, sqlCnn );
DataSet dataSet = new DataSet();
dataAdapter.Fill( dataSet );
return dataSet;
}
}
catch
{
return null;
}
}

/// <summary>
/// 3.使用資料庫內容填充DataGrid
/// </summary>
/// <param name="dataGrid">要填充的DataGrid</param>
/// <param name="strSql">要擷取資料庫內容的SQL字串</param>
/// <returns></returns>
public static bool FillDataGridFromSQLString( DataGrid dataGrid,string strSql)
{
try
{
DataSet ds = GetDataSet(strSql);
dataGrid.SetDataBinding(ds, "");

return true;
}
catch(Exception ee)
{
string t=ee.Message;
return false;
}
}

/// <summary>
/// 4.使用資料庫內容填充DataGrid
/// </summary>
/// <param name="dataGrid">要填充的DataGrid</param>
/// <param name="strSql">要擷取資料庫內容的SQL字串</param>
/// <param name="table">要添充DataGrid的表名</param>
/// <returns></returns>
public static bool FillDataGridFromSQLString( DataGrid dataGrid,string strSql,string table)
{
try
{
DataSet ds = GetDataSet(strSql);
dataGrid.SetDataBinding(ds, table);

return true;
}
catch(Exception ee)
{
string t=ee.Message;
return false;
}
}

/// <summary>
/// 5、返回SQL語句所查詢出來的行數
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public static int GetRowCount(string strSql)
{
DataSet ds = GetDataSet(strSql);
int count = ds.Tables[0].Rows.Count;
return count;
}

/// <summary>
/// 6.填充下拉式清單
/// </summary>
/// <param name="cmBox">要添充的ComboBox</param>
/// <param name="strSql">查詢語句</param>
/// <returns>是否成功</returns>
public static bool FillComboBox(ComboBox cmBox,string strSql)
{
try
{
using(SqlConnection sqlCnn = GetSqlConnection())
{
SqlDataReader dr = GetDataReader(strSql);
while(dr.Read())
{
cmBox.Items.Add(dr.GetValue(0));
}
return true;
}
}
catch
{
return false;
}
}

/// <summary>
/// 7.由一條SQL語句產生一個DataReader；傳回值需要判斷是否為空白
/// </summary>
/// <param name="strSql">要使用的SQl語句</param>
/// <returns></returns>
public static SqlDataReader GetDataReader(string strSql)
{
try
{
SqlConnection sqlCnn = GetSqlConnection();
SqlCommand sqlCmm = new SqlCommand(strSql,sqlCnn);
return sqlCmm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
return null;
}
}

/// <summary>
/// 8.返回單個查詢資料：第一列，第一行的值
/// </summary>
/// <param name="strSql">Select SQL語句</param>
/// <returns></returns>
public static string GetFirstData(string strSql)
{
try
{
using( SqlConnection sqlCnn = GetSqlConnection() )
{
SqlCommand sqlCmm = new SqlCommand(strSql,sqlCnn);
return sqlCmm.ExecuteScalar().ToString();
}
}
catch
{
return "";
}
}

/// <summary>
/// 9.對資料庫中的一條記錄操作：增、刪、更新
/// </summary>
/// <param name="strSql">要執行的SQL語句</param>
/// <returns>返回執行是否成功</returns>
public static bool ExecuteCommand(string strSql)
{
try
{
using( SqlConnection sqlCnn = GetSqlConnection())
{
SqlCommand sqlCmm = new SqlCommand(strSql,sqlCnn);
int temp = sqlCmm.ExecuteNonQuery();
return temp == 1;

}
}
catch
{
return false;
}

}

/// <summary>
/// 10.對資料庫進行增刪改操作
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public static bool ExecuteCommand2(string strSql)
{
try
{
using( SqlConnection sqlCnn = GetSqlConnection())
{
SqlCommand sqlCmm = new SqlCommand(strSql,sqlCnn);
int temp = sqlCmm.ExecuteNonQuery();
return true;
}
}
catch
{
return false;
}
}

/// <summary>
/// 11.判斷str是不是全是由數字構成
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public static bool IsNumeric(string str)
{
if (str==null || str.Length==0)
return false;
foreach(char c in str)
{
if (!Char.IsNumber(c))
{
return false;
}
}
return true;
}

/// <summary>
/// 12.檢測含有中文字串的實際長度
/// </summary>
/// <param name="str">字串</param>
public static int len(string str)
{
System.Text.ASCIIEncoding n = new System.Text.ASCIIEncoding();
byte[] b = n.GetBytes(str);
int l = 0; // l 為字串的實際長度
for (int i=0;i <= b.Length-1;i++)
{
if (b[i] ==63) //判斷是否為漢字或全腳符號
{
l++;
}
l++;
}
return l;
} }
}