|
|
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.IO; using System.Data; using System.Data.Sql; using System.Data.SqlClient; using System.Configuration; //using Npgsql;
namespace ManagementSystem.Utility { public static class SQLUtility { #region 取得指定Table資料
public static DataSet GetTable(string strTableName, string[] strColumnList , string strWhere, string strOrderBy) { DataSet dsData = new DataSet(); try { string strColumns = ""; foreach (string strColumn in strColumnList) //取得欄位陣列
{ if (strColumns != "") { strColumns += "," + strColumn.Trim(); } else { strColumns = strColumn; } } string strCommand = string.Format("Select {0} From {1} ",strColumns, strTableName);
if (strWhere != "") //設定過濾條件
{ strCommand += " Where " + strWhere; }
if (strOrderBy != "") //取得排序欄位
{ strCommand += " Order By " + strOrderBy; }
//using (SqlConnection sqlConn = GetConn(MainForm.strAccountingBookID))
//{
// using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(strCommand, sqlConn))
// {
// if (sqlConn.State == ConnectionState.Closed) //判斷連線狀態
// {
// sqlConn.Open();
// }
// sqlAdapter.Fill(dsData, "Result");
// }
//}
return dsData; } catch (Exception ex) { throw ex; } }
public static DataSet GetTable(string strTableName, string[] strColumnList, string strWhere) { return GetTable(strTableName, strColumnList, strWhere, ""); }
public static DataSet GetTable(string strTableName, string[] strColumnList) { return GetTable(strTableName, strColumnList,"",""); }
public static DataSet GetTable(string strTableName) { return GetTable(strTableName,new string[] {"*"}, "", ""); }
#endregion
#region MS-SQL命令相關程式集
//public static SqlConnection GetConn(string strOrg)
//{
// string strConn = "";
// SqlConnection sqlConn = null;
// switch(strOrg)
// {
// case "":
// strConn = System.Configuration.ConfigurationManager.ConnectionStrings["OrigtekSQLConn"].ToString();
// sqlConn = new SqlConnection(strConn);
// break;
// case "Origtek":
// strConn = System.Configuration.ConfigurationManager.ConnectionStrings["OrigtekSQLConn"].ToString();
// sqlConn = new SqlConnection(strConn);
// break;
// case "OrigtekEnergy":
// strConn = System.Configuration.ConfigurationManager.ConnectionStrings["OEnergySQLConn"].ToString();
// sqlConn = new SqlConnection(strConn);
// break;
// }
// return sqlConn;
//}
//public static SqlConnection GetConn()
//{
// return GetConn("");
//}
public static DataSet GetSQLResult(string strSQL) { DataSet dsData = new DataSet(); try { //using (SqlConnection sqlConn = GetConn(MainForm.strAccountingBookID))
//{
// using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(strSQL, sqlConn))
// {
// if (sqlConn.State == ConnectionState.Closed) //判斷連線狀態
// {
// sqlConn.Open();
// }
// sqlAdapter.Fill(dsData, "Result");
// }
//}
return dsData; } catch(Exception ex) { throw ex; } }
public static bool IsExist(string strSQL) { try { DataSet sdResult = GetSQLResult(strSQL); DataTable dtResult = sdResult.Tables[0]; if (dtResult.Rows.Count > 0) { return true; } else { return false; } } catch (Exception ex) { throw ex; } }
public static bool RunSQLNonReturn(string strSQL) { try { //SqlConnection sqlConn = GetConn(MainForm.strAccountingBookID);
//SqlCommand sqlCmd = new SqlCommand(strSQL, sqlConn);
//if (sqlConn.State == ConnectionState.Closed) //判斷連線狀態
//{
// sqlConn.Open();
//}
//sqlCmd.ExecuteNonQuery();
return true; } catch (Exception ex) { return false; } }
public static string GetSQLCount(string strSQL) { string strCount = ""; DataSet dsTemp = GetSQLResult(strSQL); strCount = dsTemp.Tables[0].Rows.Count.ToString();
//回傳查詢結果
if(strCount == "") { return "0"; } else { return strCount; } } #endregion
#region 其它共用程式
public static bool IsNumber(string strSource) { float output; return float.TryParse(strSource, out output); } //確認是否為數字
public static bool IsDate(string strSource) { try { //System.Globalization.DateTimeFormatInfo dtInfo = new System.Globalization.DateTimeFormatInfo();
//dtInfo.FullDateTimePattern = "yyyy/MM/dd";
//DateTime dtOutput = DateTime.ParseExact(strSource, "F", dtInfo);
DateTime dtOutput; if (DateTime.TryParse(strSource, out dtOutput)) { return true; } else { return false; } } catch(Exception ex) { return false; } }
public static string MarkNumber(double dbNumber,int intPoint) //將資料加上三位一撇
{ return string.Format("{0:N" + intPoint + "}", dbNumber); }
public static string MarkNumber(int intNumber, int intPoint) //將資料加上三位一撇
{ return string.Format("{0:N" + intPoint + "}", intNumber); }
public static string MarkNumber(string strNumber, int intPoint) //將資料加上三位一撇
{ if (strNumber.IndexOf("%") != -1) { return strNumber; }
if (!string.IsNullOrEmpty(strNumber)) { if (IsNumber(strNumber)) { return MarkNumber(Convert.ToDouble(strNumber), intPoint); } }
return ""; }
public static string MarkNumber(double dbNumber) //將資料加上三位一撇
{ return string.Format("{0:N0}", dbNumber); }
public static string MarkNumber(int intNumber) //將資料加上三位一撇
{ return string.Format("{0:N0}", intNumber); }
public static string MarkNumber(string strNumber) //將資料加上三位一撇
{ if (!string.IsNullOrEmpty(strNumber)) { if (strNumber.IndexOf("%") != -1) { return strNumber; }
if (IsNumber(strNumber)) { return MarkNumber(Math.Round(Convert.ToDouble(strNumber))); } } return ""; }
public static void CreateDir(string strDirPath) //建立資料夾
{ string[] strDir = strDirPath.Split('\\'); string strCreatePath = ""; foreach (string strPath in strDir) { if(strPath != "") { strCreatePath += strPath + "\\"; if (!Directory.Exists(strCreatePath)) { Directory.CreateDirectory(strCreatePath); } } } }
public static void WriteFile(string strFilePath ,string strContent) //撰寫檔案文字內容
{ using (StreamWriter swFile = new StreamWriter(strFilePath, true)) { swFile.WriteLine(strContent); } }
public static void WriteFile(string strFilePath, string[] strContent) //撰寫檔案文字內容
{ //判斷檔案是否存在
string[] strPathArray = strFilePath.Split('\\'); string strCHKPath = "";
for (int intPath = 0; intPath < strPathArray.Length - 1; intPath ++ ) { strCHKPath += strPathArray[intPath] + "\\"; }
//檢查資料夾是否存在
if (!File.Exists(strCHKPath)) { CreateDir(strCHKPath); }
using (StreamWriter swFile = new StreamWriter(strFilePath, true)) { foreach(string strContentText in strContent) { swFile.WriteLine(strContentText); } } }
#endregion
} }
|