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 TaoThuTucGet()
{
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] + "_Get]') AND type in (N'P', N'PC'))";
SQL += "\nDROP PROCEDURE [dbo].[sp_" + arrTables[0, i] + "_Get]\nGO";
SQL += "\nCREATE PROCEDURE dbo.sp_";
SQL += arrTables[0, i] + "_Get";
// Lay Khoa chinh cua bang
DataTable dtbKeyProperties = oThongTinCSDL.TimKhoa(arrTables[1, i]);
if (dtbKeyProperties.Rows.Count == 0)
SQL += "";
else
SQL += "\n(";
for (int j = 0; j < dtbKeyProperties.Rows.Count; j++)
{
if (j >= 1)
SQL += ",\n\t@" + dtbKeyProperties.Rows[j]["name"].ToString() + " " + oThongTinCSDL.GetDataType(dtbKeyProperties.Rows[j]["xtype"].ToString()) + "(" + dtbKeyProperties.Rows[j]["Length"].ToString() + ")";
else
SQL += "\n\t@" + dtbKeyProperties.Rows[j]["name"].ToString() + " " + oThongTinCSDL.GetDataType(dtbKeyProperties.Rows[j]["xtype"].ToString()) + "(" + dtbKeyProperties.Rows[j]["Length"].ToString() + ")";
}
if (dtbKeyProperties.Rows.Count == 0)
SQL += "\nAS";
else
{
SQL += "\n)\nAS";
}
if (dtbKeyProperties.Rows.Count > 0)
{
SQL += "\nBEGIN";
SQL += "\n\tIf ";
for (int j = 0; j < dtbKeyProperties.Rows.Count; j++)
{
if (j >= 1)
SQL += " AND @" + dtbKeyProperties.Rows[j]["name"].ToString() + " <= 0";
else
SQL += " @" + dtbKeyProperties.Rows[j]["name"].ToString() + " <= 0";
}
SQL += "\n\t\tSELECT * FROM " + arrTables[0, i];
SQL += "\n\tElse";
SQL += "\n\t\tSELECT * FROM " + arrTables[0, i] + " WHERE ";
for (int j = 0; j < dtbKeyProperties.Rows.Count; j++)
{
if (j >= 1)
SQL += " AND " + dtbKeyProperties.Rows[j]["name"].ToString() + " = @" + dtbKeyProperties.Rows[j]["name"].ToString();
else
SQL += dtbKeyProperties.Rows[j]["name"].ToString() + " = @" + dtbKeyProperties.Rows[j]["name"].ToString();
}
SQL += "\nEND";
}
SQL += "\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;
}