using System;
using System.Data;
using System.Data.SqlClient;
namespace SQLDBnamespace
{
public class SQLDB
{
private SqlConnection cn;
public SQLDB(SqlConnection _cn)
{
cn = _cn;
}
private object[] GetTableColumnNameAndType(string DBTableName)
{
cn.Close();
cn.Open();
SqlCommand cm = new SqlCommand("SELECT * FROM " + DBTableName, cn);
SqlDataReader dr = cm.ExecuteReader();
int TableColumnNum = dr.FieldCount;
string[] TableColumnNameArray = new string[TableColumnNum];
string[] TableColumTypeArray = new string[TableColumnNum];
for (int i = 0; i < TableColumnNum; i++)
{
TableColumnNameArray[i] = dr.GetName(i);
TableColumTypeArray[i] = dr.GetDataTypeName(i);
}
dr.Close();
dr.Dispose();
cm.Dispose();
cn.Close();
object[] temp = new object[2];
temp[0] = TableColumnNameArray;
temp[1] = TableColumTypeArray;
return temp;
}
public int GetDBTableRowLength(string DBTableName)
{
int GetDBTableRowLength = 0;
cn.Open();
SqlCommand cm = new SqlCommand("SELECT * FROM " + DBTableName, cn);
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
GetDBTableRowLength = GetDBTableRowLength + 1;
}
dr.Close();
dr.Dispose();
cm.Dispose();
cn.Close();
return GetDBTableRowLength;
}
//如果PKValue是識別ID 請在PKFlag矩陣中填2
public bool InsertTable(string SQL, int[] PKFlag, object[] InsertTableArray, string DBTableName)
{
try
{
//確認PK值都有輸入
bool PKValueIsNull = false;
int TableColumnLength = InsertTableArray.Length;
for (int i = 0; i < TableColumnLength; i++)
{
if (PKFlag[i] == 1 && InsertTableArray[i] == null)
PKValueIsNull = true;
}
if (PKValueIsNull == false)
{
object[] temp = new object[2];
string[] TableColumnName = new string[TableColumnLength];
string[] TableColumnType = new string[TableColumnLength];
temp = GetTableColumnNameAndType(DBTableName);
TableColumnName = (string[])temp[0];
TableColumnType = (string[])temp[1];
string[] cmParameter = new string[TableColumnLength];
string cmParameterstring = "";
string[] cmParameterType = new string[TableColumnLength];
for (int i = 0; i < TableColumnLength; i++)
{
if (PKFlag[i] == 2 && InsertTableArray[i] == null)
{
}
else
{
cmParameter[i] = TableColumnName[i].ToString();
cmParameterstring += '@' + TableColumnName[i].ToString() + ", ";
cmParameterType[i] = TableColumnType[i].ToString();
}
}
cmParameterstring = cmParameterstring.Remove(cmParameterstring.Length - 2, 2);
cn.Open();
SqlCommand cm = new SqlCommand("INSERT INTO " + DBTableName + " VALUES( " + cmParameterstring + " )", cn);
for (int i = 0; i < TableColumnLength; i++)
{
cm.Parameters.Add(new SqlParameter('@' + cmParameter[i], "SqlDbType." + cmParameterType[i]));
}
for (int i = 0; i < TableColumnLength; i++)
{
if (InsertTableArray[i] != null)
{
cm.Parameters['@' + cmParameter[i]].Value = InsertTableArray[i];
}
else
{
cm.Parameters['@' + cmParameter[i]].Value = DBNull.Value;
}
}
cm.ExecuteNonQuery();
cm.Dispose();
cn.Close();
return true;
}
return false;
}
catch
{
cn.Close();
return false;
}
}
public bool DeleteTable(string SQL, int DeleteNullFlag, int[] ColumnSort, object[] DeleteTableArray, string DBTableName)
{
int TableColumnLength = DeleteTableArray.Length;
int DeleteTableArrayParameterNum = 0;
int[] ColumnSortFlagAndLocation = new int[2] { 0, 0 };
object[] temp = new object[2];
string[] TableColumnName = new string[TableColumnLength];
string[] TableColumnType = new string[TableColumnLength];
temp = GetTableColumnNameAndType(DBTableName);
TableColumnName = (string[])temp[0];
TableColumnType = (string[])temp[1];
for (int i = 0; i < TableColumnLength; i++)
{
if (DeleteNullFlag == 1)
DeleteTableArrayParameterNum = DeleteTableArrayParameterNum + 1;
else if (DeleteTableArray[i] != null)
DeleteTableArrayParameterNum = DeleteTableArrayParameterNum + 1;
}
string[] cmParameter = new string[DeleteTableArrayParameterNum];
string cmParameterstring = "";
string ColumnSortName = "";
string[] cmParameterType = new string[DeleteTableArrayParameterNum];
int ParameterCounter = 0;
bool FirstWhere = false;
for (int i = 0; i < TableColumnLength; i++)
{
if (DeleteTableArray[i] != null)
{
if (FirstWhere == false)
{
cmParameterstring += " WHERE ";
FirstWhere = true;
}
cmParameter[ParameterCounter] = TableColumnName[i].ToString();
cmParameterType[ParameterCounter] = TableColumnType[i].ToString();
cmParameterstring += TableColumnName[i].ToString() + "=@" + TableColumnName[i].ToString() + " AND ";
ParameterCounter = ParameterCounter + 1;
}
if (ColumnSort[i] == 1)
{
ColumnSortFlagAndLocation[0] = 1;
ColumnSortFlagAndLocation[1] = i;
ColumnSortName = TableColumnName[i].ToString();
}
}
cmParameterstring = cmParameterstring.Remove(cmParameterstring.Length - 5, 5);
cn.Open();
SqlCommand cm = new SqlCommand("DELETE FROM " + DBTableName + cmParameterstring + " ", cn);
for (int i = 0; i < ParameterCounter; i++)
{
cm.Parameters.Add(new SqlParameter('@' + cmParameter[i], Convert(cmParameterType[i])));
}
ParameterCounter = 0;
for (int i = 0; i < TableColumnLength; i++)
{
if (DeleteTableArray[i] != null)
{
cm.Parameters['@' + cmParameter[ParameterCounter]].Value = DeleteTableArray[i];
ParameterCounter = ParameterCounter + 1;
}
else if (DeleteNullFlag == 1)
{
cm.Parameters['@' + cmParameter[ParameterCounter]].Value = DBNull.Value;
ParameterCounter = ParameterCounter + 1;
}
}
cm.ExecuteNonQuery();
cm.Dispose();
cn.Close();
if (ColumnSortFlagAndLocation[0] == 1)
{
bool SortTableFlag = SortTable(ColumnSort, DBTableName);
}
return true;
}
public bool SortTable(int[] ColumnSort, string DBTableName)
{
int TableColumnLength = ColumnSort.Length;
int[] ColumnSortFlagAndLocation = new int[2] { 0, 0 };
object[] temp = new object[2];
string[] TableColumnName = new string[TableColumnLength];
string ColumnSortName = "";
temp = GetTableColumnNameAndType(DBTableName);
TableColumnName = (string[])temp[0];
for (int i = 0; i < TableColumnLength; i++)
{
if (ColumnSort[i] == 1)
{
ColumnSortFlagAndLocation[0] = 1;
ColumnSortFlagAndLocation[1] = i;
ColumnSortName = TableColumnName[i].ToString();
}
int DBTableRowLength = GetDBTableRowLength(DBTableName);
int[] intTempID = new int[DBTableRowLength];
int Counter = 0;
cn.Open();
SqlCommand cm = new SqlCommand("SELECT " + ColumnSortName + " FROM " + DBTableName, cn);
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
intTempID[Counter] = int.Parse(dr[0].ToString());
Counter = Counter + 1;
}
dr.Dispose();
cm.Dispose();
cn.Close();
Counter = 0;
while (Counter != DBTableRowLength)
{
object[] UpdateInTableArray = new object[TableColumnLength];
object[] UpdateSetTableArray = new object[TableColumnLength];
UpdateInTableArray[ColumnSortFlagAndLocation[1]] = intTempID[Counter];
UpdateSetTableArray[ColumnSortFlagAndLocation[1]] = 1000 + Counter;
UpdateTable("", 0, UpdateInTableArray, UpdateSetTableArray, DBTableName);
Counter = Counter + 1;
}
Counter = 0;
while (Counter != DBTableRowLength)
{
object[] UpdateInTableArray = new object[TableColumnLength];
object[] UpdateSetTableArray = new object[TableColumnLength];
UpdateInTableArray[ColumnSortFlagAndLocation[1]] = 1000 + Counter;
UpdateSetTableArray[ColumnSortFlagAndLocation[1]] = Counter;
UpdateTable("", 0, UpdateInTableArray, UpdateSetTableArray, DBTableName);
Counter = Counter + 1;
}
}
return true;
}
//只有Update的null要用"NULL"代替
public bool UpdateTable(string SQL, int UpdateNullFlag, object[] UpdateInTableArray, object[] UpdateSetTableArray, string DBTableName)
{
int TableColumnLength = UpdateInTableArray.Length;
int UpdateInTableArrayParameterNum = 0;
int UpdateSetTableArrayParameterNum = 0;
object[] temp = new object[2];
string[] TableColumnName = new string[TableColumnLength];
string[] TableColumnType = new string[TableColumnLength];
temp = GetTableColumnNameAndType(DBTableName);
TableColumnName = (string[])temp[0];
TableColumnType = (string[])temp[1];
for (int i = 0; i < TableColumnLength; i++)
{
if (UpdateNullFlag == 1)
UpdateInTableArrayParameterNum = UpdateInTableArrayParameterNum + 1;
else if (UpdateInTableArray[i] != null)
UpdateInTableArrayParameterNum = UpdateInTableArrayParameterNum + 1;
if (UpdateNullFlag == 1)
UpdateSetTableArrayParameterNum = UpdateSetTableArrayParameterNum + 1;
else if (UpdateSetTableArray[i] != null)
UpdateSetTableArrayParameterNum = UpdateSetTableArrayParameterNum + 1;
}
string[] cmInParameter = new string[UpdateInTableArrayParameterNum];
string[] cmSetParameter = new string[UpdateSetTableArrayParameterNum];
string cmInParameterstring = "";
string cmSetParameterstring = "";
string[] cmInParameterType = new string[UpdateInTableArrayParameterNum];
string[] cmSetParameterType = new string[UpdateSetTableArrayParameterNum];
int InParameterCounter = 0;
int SetParameterCounter = 0;
bool FirstWhere = false;
for (int i = 0; i < TableColumnLength; i++)
{
if (UpdateInTableArray[i] != null)
{
if (FirstWhere == false)
{
cmInParameterstring += " WHERE ";
FirstWhere = true;
}
cmInParameter[InParameterCounter] = TableColumnName[i].ToString() + "Origin";
cmInParameterstring += TableColumnName[i].ToString() + "=@" + TableColumnName[i].ToString() + "Origin" + " AND ";
cmInParameterType[InParameterCounter] = TableColumnType[i].ToString();
InParameterCounter = InParameterCounter + 1;
}
if (UpdateSetTableArray[i] != null)
{
cmSetParameter[SetParameterCounter] = TableColumnName[i].ToString();
cmSetParameterstring += TableColumnName[i].ToString() + "=@" + TableColumnName[i].ToString() + ", ";
cmSetParameterType[SetParameterCounter] = TableColumnType[i].ToString();
SetParameterCounter = SetParameterCounter + 1;
}
}
cmInParameterstring = cmInParameterstring.Remove(cmInParameterstring.Length - 5, 5);
cmSetParameterstring = cmSetParameterstring.Remove(cmSetParameterstring.Length - 2, 2);
cn.Open();
SqlCommand cm = new SqlCommand("UPDATE " + DBTableName + " SET " + cmSetParameterstring + cmInParameterstring, cn);
for (int i = 0; i < InParameterCounter; i++)
{
cm.Parameters.Add(new SqlParameter('@' + cmInParameter[i], Convert(cmInParameterType[i])));
}
for (int i = 0; i < SetParameterCounter; i++)
{
cm.Parameters.Add(new SqlParameter('@' + cmSetParameter[i], Convert(cmSetParameterType[i])));
}
InParameterCounter = 0;
SetParameterCounter = 0;
for (int i = 0; i < TableColumnLength; i++)
{
if (UpdateInTableArray[i] != null)
{
cm.Parameters['@' + cmInParameter[InParameterCounter]].Value = UpdateInTableArray[i];
InParameterCounter = InParameterCounter + 1;
}
else if (UpdateNullFlag == 1)
{
cm.Parameters['@' + cmInParameter[InParameterCounter]].Value = DBNull.Value;
InParameterCounter = InParameterCounter + 1;
}
if (UpdateSetTableArray[i] != null && UpdateSetTableArray[i].ToString() != "NULL")
{
cm.Parameters['@' + cmSetParameter[SetParameterCounter]].Value = UpdateSetTableArray[i];
SetParameterCounter = SetParameterCounter + 1;
}
else if (UpdateSetTableArray[i] != null && UpdateSetTableArray[i].ToString() == "NULL")
{
cm.Parameters['@' + cmSetParameter[SetParameterCounter]].Value = DBNull.Value;
SetParameterCounter = SetParameterCounter + 1;
}
else if (UpdateNullFlag == 1)
{
cm.Parameters['@' + cmSetParameter[SetParameterCounter]].Value = DBNull.Value;
SetParameterCounter = SetParameterCounter + 1;
}
}
cm.ExecuteNonQuery();
cm.Dispose();
cn.Close();
return true;
}
public object[] SelectTable(string SQL, int SelectInNullFlag, object[] SelectInTableArray, object[] SelectOutTableArray, string DBTableName)
{
if (SQL == "")
{
int TableColumnLength = SelectInTableArray.Length;
int SelectInTableArrayParameterNum = 0;
int SelectOutTableArrayParameterNum = 0;
object[] temp = new object[2];
string[] TableColumnName = new string[TableColumnLength];
string[] TableColumnType = new string[TableColumnLength];
temp = GetTableColumnNameAndType(DBTableName);
TableColumnName = (string[])temp[0];
TableColumnType = (string[])temp[1];
for (int i = 0; i < TableColumnLength; i++)
{
if (SelectInNullFlag == 1)
SelectInTableArrayParameterNum = SelectInTableArrayParameterNum + 1;
else if (SelectInTableArray[i] != null)
SelectInTableArrayParameterNum = SelectInTableArrayParameterNum + 1;
if (SelectOutTableArray[i] != null)
SelectOutTableArrayParameterNum = SelectOutTableArrayParameterNum + 1;
}
string[] cmInParameter = new string[SelectInTableArrayParameterNum];
string[] cmOutParameter = new string[SelectOutTableArrayParameterNum];
int[] SelectOutDataTypeSize = new int[SelectOutTableArrayParameterNum];
string cmInParameterstring = "";
string cmOutParameterstring = "";
string[] cmInParameterType = new string[SelectInTableArrayParameterNum];
string[] cmOutParameterType = new string[SelectOutTableArrayParameterNum];
int InParameterCounter = 0;
int OutParameterCounter = 0;
bool FirstWhere = false;
for (int i = 0; i < TableColumnLength; i++)
{
if (SelectInTableArray[i] != null)
{
if (FirstWhere == false)
{
cmInParameterstring += " WHERE ";
FirstWhere = true;
}
cmInParameter[InParameterCounter] = TableColumnName[i].ToString() + "Origin";
cmInParameterstring += TableColumnName[i].ToString() + "=@" + TableColumnName[i].ToString() + "Origin" + " AND ";
cmInParameterType[InParameterCounter] = TableColumnType[i].ToString();
InParameterCounter = InParameterCounter + 1;
}
if (SelectOutTableArray[i] != null)
{
cmOutParameter[OutParameterCounter] = TableColumnName[i].ToString();
cmOutParameterstring += '@' + TableColumnName[i].ToString() + '=' + TableColumnName[i].ToString() + ", ";
cmOutParameterType[OutParameterCounter] = TableColumnType[i].ToString();
if ((int)SelectOutTableArray[i] > 0)
{
SelectOutDataTypeSize[OutParameterCounter] = (int)SelectOutTableArray[i];
}
OutParameterCounter = OutParameterCounter + 1;
}
}
cmInParameterstring = cmInParameterstring.Remove(cmInParameterstring.Length - 5, 5);
cmOutParameterstring = cmOutParameterstring.Remove(cmOutParameterstring.Length - 2, 2);
cn.Open();
SqlCommand cm = new SqlCommand("SELECT " + cmOutParameterstring + " FROM " + DBTableName + cmInParameterstring, cn);
for (int i = 0; i < InParameterCounter; i++)
{
cm.Parameters.Add(new SqlParameter('@' + cmInParameter[i], Convert(cmInParameterType[i])));
}
for (int i = 0; i < OutParameterCounter; i++)
{
if (SelectOutDataTypeSize[i] == 0)
cm.Parameters.Add(new SqlParameter('@' + cmOutParameter[i], Convert(cmOutParameterType[i])));
else
{
cm.Parameters.Add(new SqlParameter('@' + cmOutParameter[i], Convert(cmOutParameterType[i]), SelectOutDataTypeSize[i]));
}
cm.Parameters['@' + cmOutParameter[i]].Direction = ParameterDirection.Output;
}
InParameterCounter = 0;
OutParameterCounter = 0;
for (int i = 0; i < TableColumnLength; i++)
{
if (SelectInTableArray[i] != null)
{
cm.Parameters['@' + cmInParameter[InParameterCounter]].Value = SelectInTableArray[i];
InParameterCounter = InParameterCounter + 1;
}
else if (SelectInNullFlag == 1)
{
cm.Parameters['@' + cmInParameter[InParameterCounter]].Value = DBNull.Value;
InParameterCounter = InParameterCounter + 1;
}
}
cm.ExecuteNonQuery();
for (int i = 0; i < TableColumnLength; i++)
{
if (SelectOutTableArray[i] != null)
{
SelectOutTableArray[i] = cm.Parameters['@' + cmOutParameter[OutParameterCounter]].Value;
OutParameterCounter = OutParameterCounter + 1;
}
}
cm.Dispose();
cn.Close();
return SelectOutTableArray;
}
else if (SQL != "")
{
//將變數放進cmInParameter 還有找有幾個select欄位
object[] cmInParameter = new object[SelectInTableArray.Length];
object[] cmInParameterType = new object[SelectInTableArray.Length];
object[] cmInComnName = new object[SelectInTableArray.Length];
int cmOutParameter = 1;
int AtCounter = 0;
for (int i = 0; i < SQL.Length; i++)
{
//cmInComnName
if (SQL[i] == ' ')
{
string strtemp = SQL.Substring(i, SQL.Length - i);
if (strtemp.IndexOf('@') != -1)
{
cmInComnName[AtCounter] = SQL.Substring(i, SQL.IndexOf("=@", i) - i).Replace(" ", "");
}
}
if (SQL.IndexOf(" WHERE", i) != -1)
{
if (SQL[i] == ',')
cmOutParameter = cmOutParameter + 1;
if (SQL[i] == '*')
cmOutParameter = -1;
}
//cmInParameter
if (SQL[i] == '@')
{
if (SQL.IndexOf(" ", i) > -1)
cmInParameter[AtCounter] = SQL.Substring(i, SQL.IndexOf(" ", i) - i);
else
cmInParameter[AtCounter] = SQL.Substring(i, SQL.Length - i);
AtCounter = AtCounter + 1;
}
}
//找欄位名跟型別
object[] temp = new object[2];
string strFROM = SQL.Substring(SQL.IndexOf("FROM"), SQL.IndexOf(" WHERE") - SQL.IndexOf("FROM"));
strFROM = strFROM.Replace("FROM ", "");
temp = GetTableColumnNameAndType(strFROM);
string[] TableColumnName = (string[])temp[0];
string[] TableColumnType = (string[])temp[1];
int Counter = 0;
for (int i = 0; i < TableColumnName.Length; i++)
{
if (TableColumnName[i].ToString() == cmInComnName[Counter].ToString())
{
cmInParameterType[Counter] = TableColumnType[i];
if (Counter + 1 < AtCounter)
Counter = Counter + 1;
}
}
//如果是SELECT *
if (cmOutParameter == -1)
cmOutParameter = TableColumnName.Length;
//讀有幾列
SqlCommand cm = new SqlCommand(SQL, cn);
for (int i = 0; i < AtCounter; i++)
{
cm.Parameters.Add(new SqlParameter(cmInParameter[i].ToString(), Convert(cmInParameterType[i].ToString()))).Value = SelectInTableArray[i];
}
cn.Open();
SqlDataReader dr = cm.ExecuteReader();
int RowCounter = 0;
while (dr.Read())
{
RowCounter = RowCounter + 1;
}
dr.Close();
dr.Dispose();
cn.Close();
object[,] SelectOutTableArray2 = new object[RowCounter, cmOutParameter];
//正式放進去
cn.Open();
dr = cm.ExecuteReader();
RowCounter = 0;
while (dr.Read())
{
for (int i = 0; i < cmOutParameter; i++)
{
SelectOutTableArray2[RowCounter, i] = dr[i];
}
RowCounter = RowCounter + 1;
}
dr.Close();
dr.Dispose();
cn.Close();
SelectOutTableArray[0] = SelectOutTableArray2;
return SelectOutTableArray;
}
return null;
}
private SqlDbType Convert(string TypeName)
{
switch (TypeName)
{
case "int":
return System.Data.SqlDbType.Int;
case "nvarchar":
return System.Data.SqlDbType.NVarChar;
case "varchar":
return System.Data.SqlDbType.VarChar;
case "char":
return System.Data.SqlDbType.Char;
case "datetime":
return System.Data.SqlDbType.DateTime;
default:
return System.Data.SqlDbType.Int;
}
}
}
}
Submit an article or tip