public void TaoLopEntity()
{
// Lay danh sach bang
//string strCode = "";
string[,] arrTables = oThongTinCSDL.GetTableNameId();
for (int i = 0; i < arrTables.GetLength(1); i++)
{
// Lay danh sach cot cua bang
DataTable dtbColumns = oThongTinCSDL.GetColunms(arrTables[1, i]);
TaoLop(arrTables[0, i], dtbColumns);
}
}
public void TaoLopBaseData()
{
// Lay danh sach bang
string[,] arrTables = oThongTinCSDL.GetTableNameId();
for (int i = 0; i < arrTables.GetLength(1); i++)
{
// Lay danh sach cot cua bang
DataTable dtbColumns = oThongTinCSDL.GetColunms(arrTables[1, i]);
DataTable dtbKeyColumns = oThongTinCSDL.TimKhoa(arrTables[1, i]);
TaoLopBase(arrTables[0, i], dtbColumns, dtbKeyColumns);
}
}
public string TaoThuTucAdd()
{
string SQL = "";
string[,] arrTables = oThongTinCSDL.GetTableNameId();
for (int i = 0; i < arrTables.GetLength(1); i++)
{
SQL += "\nIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_" + arrTables[0, i] + "_Add]') AND type in (N'P', N'PC'))";
SQL += "\nDROP PROCEDURE [dbo].[sp_" + arrTables[0, i] + "_Add]\nGO";
SQL += "\nCREATE PROCEDURE dbo.sp_";
SQL += arrTables[0, i] + "_Add";
// Lay Khoa chinh cua bang
DataTable dtbProperties = oThongTinCSDL.GetColunms(arrTables[1, i]);
SQL += "\n(";
dtbProperties.DefaultView.RowFilter = "colstat = 0";
for (int j = 0; j < dtbProperties.DefaultView.Count; j++)
{
if (j >= 1)
SQL += ",\n\t@" + dtbProperties.DefaultView[j]["name"].ToString() + " " + oThongTinCSDL.GetDataType(dtbProperties.DefaultView[j]["xtype"].ToString()) + "(" + dtbProperties.DefaultView[j]["Length"].ToString() + ")" + isNull(int.Parse(dtbProperties.DefaultView[j]["isnullable"].ToString()));
else
SQL += "\n\t@" + dtbProperties.DefaultView[j]["name"].ToString() + " " + oThongTinCSDL.GetDataType(dtbProperties.DefaultView[j]["xtype"].ToString()) + "(" + dtbProperties.DefaultView[j]["Length"].ToString() + ")" + isNull(int.Parse(dtbProperties.DefaultView[j]["isnullable"].ToString()));
}
SQL += ",\n\t@ID int output";
SQL += "\n)\nAS";
SQL += "\nBEGIN";
// Kiểm tra nếu là trường datetime có thể null thì phải xem xét với giá trị mặc định của null là ngày 1/1/1900
for (int j = 0; j < dtbProperties.DefaultView.Count; j++)
{
if (oThongTinCSDL.GetDataType(dtbProperties.DefaultView[j]["xtype"].ToString()).ToLower() == "datetime"
&& isNull(int.Parse(dtbProperties.DefaultView[j]["isnullable"].ToString())) == " = null")
{
SQL += "\n\tIf @" + dtbProperties.DefaultView[j]["name"].ToString() + " = Convert(datetime,'1/1/1900')";
SQL += "\n\t\tSET @" + dtbProperties.DefaultView[j]["name"].ToString() + " = null";
}
}
SQL += "\n\tINSERT INTO " + arrTables[0, i] + "(";
for (int j = 0; j < dtbProperties.DefaultView.Count; j++)
{
if (j >= 1)
SQL += "," + dtbProperties.DefaultView[j]["name"].ToString();
else
SQL += "" + dtbProperties.DefaultView[j]["name"].ToString();
}
SQL += ")";
SQL += "\n\tVALUES(";
for (int j = 0; j < dtbProperties.DefaultView.Count; j++)
{
if (j >= 1)
SQL += ",@" + dtbProperties.DefaultView[j]["name"].ToString();
else
SQL += "@" + dtbProperties.DefaultView[j]["name"].ToString();
}
SQL += ")";
SQL += "\n\tSELECT @ID = @@Identity";
SQL += "\nEND\nGO";
}
SQL = SQL.Replace("int(4)", "int");
SQL = SQL.Replace("datetime(8)", "datetime");
SQL = SQL.Replace("money(8)", "money");
SQL = SQL.Replace("float(8)", "float");
SQL = SQL.Replace("bigint(8)", "bigint");
SQL = SQL.Replace("bit(1)", "bit");
SQL = SQL.Replace("image(16)", "image");
SQL = SQL.Replace("ntext(16)", "ntext");
SQL = SQL.Replace("real(4)", "real");
return SQL;
}