private void btnModelAndDbClass_Click(object sender, EventArgs e)
{
try
{
string filePath = txtFilePath.Text;
string tableName = txtTableName.Text;
string namespacePath = "";
if (filePath.Substring(filePath.Length - 1).Equals("\\"))
{
string sonSlashAtilmisStr = filePath.Remove(filePath.Length - 1);
namespacePath = sonSlashAtilmisStr.Split('\\').Last();
}
else
{
namespacePath = filePath.Split('\\').Last();
}
List <ParameterSpeciality> parameters = new List <ParameterSpeciality>();
List <ParameterSpeciality> whereList = new List <ParameterSpeciality>();
DataTable dtTable = (DataTable)grd.DataSource;
List <string> columnInfo = new List <string>();
List <string> constraintInfo = new List <string>();
foreach (DataRow dr in dtTable.Rows)
{
string columnName = CommonHelper.Nvl(dr["COLUMNNAME"]);
string dataType = CommonHelper.Nvl(dr["DATATYPE"]);
int columnLengthStrıng = CommonHelper.ToInt32(dr["COLUMNLENGTHSTRING"]);
int columnLengthInt1 = CommonHelper.ToInt32(dr["COLUMNLENGTHINT1"]);
int columnLengthInt2 = CommonHelper.ToInt32(dr["COLUMNLENGTHINT2"]);
bool whereBool = Convert.ToBoolean(dr["WHEREFORUPDATE"]);
bool primaryKey = Convert.ToBoolean(dr["PRIMARYKEY"]);
string foreignKeyName = CommonHelper.Nvl(dr["FOREIGNKEYNAME"]);
ParameterSpeciality ParameterSpeciality;
if (columnLengthStrıng > 0 && dataType.Contains("CHAR"))
{
ParameterSpeciality = new ParameterSpeciality {
dbType = DbType.String, ColumnName = columnName
};
}
else if (columnLengthInt1 > 0 && columnLengthInt2 > 0)
{
ParameterSpeciality = new ParameterSpeciality {
dbType = DbType.Decimal, ColumnName = columnName
};
}
else if (columnLengthInt1 > 8)
{
ParameterSpeciality = new ParameterSpeciality {
dbType = DbType.Int64, ColumnName = columnName
};
}
else if (columnLengthInt1 > 1)
{
ParameterSpeciality = new ParameterSpeciality {
dbType = DbType.Int32, ColumnName = columnName
};
}
else if (columnLengthInt1 == 1)
{
ParameterSpeciality = new ParameterSpeciality {
dbType = DbType.Int16, ColumnName = columnName
};
}
else
{
if (dataType.Contains("DATE"))
{
ParameterSpeciality = new ParameterSpeciality {
dbType = DbType.DateTime, ColumnName = columnName
}
}
;
else
{
ParameterSpeciality = new ParameterSpeciality {
dbType = DbType.Binary, ColumnName = columnName
}
};
}
if (primaryKey)
{
ParameterSpeciality.PrimaryKey = true;
}
parameters.Add(ParameterSpeciality);
if (whereBool)
{
whereList.Add(ParameterSpeciality);
}
}
string modelClass = ModelAndDbTemplate.modeltemplate(namespacePath, tableName, parameters);
Directory.CreateDirectory(filePath + "\\" + tableName);
string modelClassName = tableName;
File.WriteAllText(filePath + "\\" + tableName + "\\" + modelClassName + ".cs", modelClass);
string dbClass = ModelAndDbTemplate.dbtemplate(namespacePath, tableName, parameters, whereList);
string dbClassName = "dbOperations" + tableName;
File.WriteAllText(filePath + "\\" + tableName + "\\" + dbClassName + ".cs", dbClass);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, Text);
}
}
public static string dbtemplate(string namespacePath, string tableName, List <ParameterSpeciality> parameters,
List <ParameterSpeciality> whereList)
{
string newLine = Environment.NewLine;
string tableObj = char.ToLowerInvariant(tableName[0]) + tableName.Substring(1);
List <string> selectList = new List <string>();
List <string> selectWithAtList = new List <string>();
List <string> updateStrList = new List <string>();
List <string> dtStartList = new List <string>();
List <string> dbReturnList = new List <string>();
List <string> dbModelList = new List <string>();
List <string> insUpdPrmList = new List <string>();
ParameterSpeciality primaryColumn = new ParameterSpeciality();
foreach (var prm in parameters)
{
if (prm.PrimaryKey)
{
primaryColumn = prm;
}
selectList.Add(prm.ColumnName);
selectWithAtList.Add("@" + prm.ColumnName);
updateStrList.Add(prm.ColumnName + " = @" + prm.ColumnName);
if (prm.dbType.Equals(DbType.String))
{
dtStartList.Add("dtList.Columns.Add(\"" + prm.ColumnName + "\", typeof(string));");
dbReturnList.Add("string " + prm.ColumnName + " = CommonHelper.Nvl(dt.Rows[i][\"" + prm.ColumnName + "\"]);");
insUpdPrmList.Add("parameters.Add(new DbParameter(\"" + prm.ColumnName + "\", DbType.String, " + tableObj + "." + prm.ColumnName + "));");
dbModelList.Add(tableObj + "." + prm.ColumnName + " = CommonHelper.Nvl(dr[\"" + prm.ColumnName + "\"]);");
}
if (prm.dbType.Equals(DbType.Decimal))
{
dtStartList.Add("dtList.Columns.Add(\"" + prm.ColumnName + "\", typeof(decimal));");
dbReturnList.Add("decimal " + prm.ColumnName + " = CommonHelper.ToDecimal(dt.Rows[i][\"" + prm.ColumnName + "\"]);");
insUpdPrmList.Add("parameters.Add(new DbParameter(\"" + prm.ColumnName + "\", DbType.Decimal, " + tableObj + "." + prm.ColumnName + "));");
dbModelList.Add(tableObj + "." + prm.ColumnName + " = CommonHelper.ToDecimal(dr[\"" + prm.ColumnName + "\"]);");
}
if (prm.dbType.Equals(DbType.Int64))
{
dtStartList.Add("dtList.Columns.Add(\"" + prm.ColumnName + "\", typeof(long));");
dbReturnList.Add("long " + prm.ColumnName + " = CommonHelper.ToInt64(dt.Rows[i][\"" + prm.ColumnName + "\"]);");
insUpdPrmList.Add("parameters.Add(new DbParameter(\"" + prm.ColumnName + "\", DbType.Int64, " + tableObj + "." + prm.ColumnName + "));");
dbModelList.Add(tableObj + "." + prm.ColumnName + " = CommonHelper.ToInt64(dr[\"" + prm.ColumnName + "\"]);");
}
if (prm.dbType.Equals(DbType.Int32))
{
dtStartList.Add("dtList.Columns.Add(\"" + prm.ColumnName + "\", typeof(int));");
dbReturnList.Add("int " + prm.ColumnName + " = CommonHelper.ToInt32(dt.Rows[i][\"" + prm.ColumnName + "\"]);");
insUpdPrmList.Add("parameters.Add(new DbParameter(\"" + prm.ColumnName + "\", DbType.Int32, " + tableObj + "." + prm.ColumnName + "));");
dbModelList.Add(tableObj + "." + prm.ColumnName + " = CommonHelper.ToInt32(dr[\"" + prm.ColumnName + "\"]);");
}
if (prm.dbType.Equals(DbType.Int16))
{
dtStartList.Add("dtList.Columns.Add(\"" + prm.ColumnName + "\", typeof(bool));");
dbReturnList.Add("bool " + prm.ColumnName + " = Convert.ToBoolean(dt.Rows[i][\"" + prm.ColumnName + "\"]);");
insUpdPrmList.Add("parameters.Add(new DbParameter(\"" + prm.ColumnName + "\", DbType.Int16, " + tableObj + "." + prm.ColumnName + "));");
dbModelList.Add(tableObj + "." + prm.ColumnName + " = CommonHelper.ToInt16(dr[\"" + prm.ColumnName + "\"]);");
}
if (prm.dbType.Equals(DbType.DateTime))
{
dtStartList.Add("dtList.Columns.Add(\"" + prm.ColumnName + "\", typeof(DateTime));");
dbReturnList.Add("DateTime " + prm.ColumnName + " = CommonHelper.ToDateTime(dt.Rows[i][\"" + prm.ColumnName + "\"]);");
insUpdPrmList.Add("parameters.Add(new DbParameter(\"" + prm.ColumnName + "\", DbType.DateTime, " + tableObj + "." + prm.ColumnName + "));");
dbModelList.Add(tableObj + "." + prm.ColumnName + " = CommonHelper.ToDateTime(dr[\"" + prm.ColumnName + "\"]);");
}
if (prm.dbType.Equals(DbType.Binary))
{
dtStartList.Add("dtList.Columns.Add(\"" + prm.ColumnName + "\", typeof(byte[]));");
dbReturnList.Add("byte[] " + prm.ColumnName + " = CommonHelper.ToByteArray(dt.Rows[i][\"" + prm.ColumnName + "\"]);");
insUpdPrmList.Add("parameters.Add(new DbParameter(\"" + prm.ColumnName + "\", DbType.Binary, " + tableObj + "." + prm.ColumnName + "));");
dbModelList.Add(tableObj + "." + prm.ColumnName + " = CommonHelper.ToByteArray(dr[\"" + prm.ColumnName + "\"]);");
}
}
string template = @"using DbConnection;
using System;
using System.Data;
using System.Collections.Generic;
namespace " + namespacePath + "." + tableName + @"
{
public class dbOperations" + tableName + @"
{
public DataTable get" + tableName + @"List()
{
DataTable dtList = new DataTable();
";
template = template + "string sql = @\"SELECT " + string.Join(", ", selectList) + @"
FROM " + tableName.ToUpperInvariant() + "\"" + @";
using (Database db = new Database())
{
DataTable dt = db.ExecuteDataTable(sql);
" + string.Join(newLine, dtStartList) + @"
for (int i = 0; i < dt.Rows.Count; i++)
{
" + string.Join(newLine, dbReturnList) + @"
dtList.Rows.Add(new object[] { " + string.Join(", ", selectList) + @" });
}
return dtList;
}
}
";
foreach (var whereObj in whereList)
{
if (whereObj.PrimaryKey)
{
template = template + @"
public " + tableName + " get" + tableName + "By" + whereObj.ColumnName + "(" +
whereObj.dbType.ToString() + " " + whereObj.ColumnName + @")
{
" + "string sql = @\"SELECT " + string.Join(", ", selectList) + @"
FROM " + tableName.ToUpperInvariant() + @"
WHERE " + whereObj.ColumnName + " = @" + whereObj.ColumnName + "\";" + @"
List<DbParameter> parameters = new List<DbParameter>();
";
string dbWhereParemeterPrimaryKey = "";
if (whereObj.dbType.Equals(DbType.String))
{
dbWhereParemeterPrimaryKey = dbWhereParemeterPrimaryKey + "parameters.Add(new DbParameter(\"" +
whereObj.ColumnName + "\", DbType.String, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Decimal))
{
dbWhereParemeterPrimaryKey = dbWhereParemeterPrimaryKey + "parameters.Add(new DbParameter(\"" +
whereObj.ColumnName + "\", DbType.Decimal, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Int64))
{
dbWhereParemeterPrimaryKey = dbWhereParemeterPrimaryKey + "parameters.Add(new DbParameter(\"" +
whereObj.ColumnName + "\", DbType.Int64, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Int32))
{
dbWhereParemeterPrimaryKey = dbWhereParemeterPrimaryKey + "parameters.Add(new DbParameter(\"" +
whereObj.ColumnName + "\", DbType.Int32, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Int16))
{
dbWhereParemeterPrimaryKey = dbWhereParemeterPrimaryKey + "parameters.Add(new DbParameter(\"" +
whereObj.ColumnName + "\", DbType.Int16, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.DateTime))
{
dbWhereParemeterPrimaryKey = dbWhereParemeterPrimaryKey + "parameters.Add(new DbParameter(\"" +
whereObj.ColumnName + "\", DbType.DateTime, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Binary))
{
dbWhereParemeterPrimaryKey = dbWhereParemeterPrimaryKey + "parameters.Add(new DbParameter(\"" +
whereObj.ColumnName + "\", DbType.Binary, " + whereObj.ColumnName + "));" + newLine;
}
template = template + dbWhereParemeterPrimaryKey + @"
using (Database db = new Database())
{
DataTable dt = db.ExecuteDataTable(sql, parameters.ToArray());
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
" + tableName + " " + tableObj + " = new " + tableName + @"();
" + string.Join(newLine, dbModelList) + @"
return " + tableObj + @";
}
}
return null;
}" + newLine;
}
template = template + @"
public DataTable get" + tableName + "ListBy" + whereObj.ColumnName + "(" +
whereObj.dbType.ToString() + " " + whereObj.ColumnName + @")
{
" + "string sql = @\"SELECT " + string.Join(", ", selectList) + @"
FROM " + tableName.ToUpperInvariant() + @"
WHERE " + whereObj.ColumnName + " = @" + whereObj.ColumnName + "\";" + @"
List<DbParameter> parameters = new List<DbParameter>();
";
string dbWhereParemeter = "";
if (whereObj.dbType.Equals(DbType.String))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName + "\", DbType.String, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Decimal))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName + "\", DbType.Decimal, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Int64))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName + "\", DbType.Int64, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Int32))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName + "\", DbType.Int32, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Int16))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName + "\", DbType.Int16, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.DateTime))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName + "\", DbType.DateTime, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Binary))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" +
whereObj.ColumnName + "\", DbType.Binary, " + whereObj.ColumnName + "));" + newLine;
}
template = template + dbWhereParemeter + @"
using (Database db = new Database())
{
return db.ExecuteDataTable(sql, parameters.ToArray());
}
}" + newLine;
}
template = template + @"
public bool " + tableName + "Insert(" + tableName + " " + tableObj + @")
{
" + "string sql = @\"INSERT INTO " + tableName.ToUpperInvariant() + @"
(" + string.Join(", ", selectList) + @")
VALUES (" + string.Join(", ", selectWithAtList) + ")\";" + @"
List<DbParameter> parameters = new List<DbParameter>();
";
template = template + string.Join(newLine, insUpdPrmList) + newLine + @"
using (Database db = new Database())
{
return db.ExecuteNonQuery(sql, parameters.ToArray()) > 0;
}
}" + newLine;
foreach (var whereObj in whereList)
{
template = template + @"
public bool " + tableName + "UpdateBy" + whereObj.ColumnName + "(" + tableName + " " + tableObj + "," +
whereObj.dbType.ToString() + " " + whereObj.ColumnName + @")
{
";
List <string> updateStrListOzel = new List <string>();
foreach (var updateStr in updateStrList)
{
if (!updateStr.Substring(0, updateStr.IndexOf(" =")).Equals(whereObj.ColumnName))
{
updateStrListOzel.Add(updateStr);
}
}
List <string> insUpdPrmListOzel = new List <string>();
foreach (var insUpdPrm in insUpdPrmList)
{
if (!insUpdPrm.Contains("\"" + whereObj.ColumnName + "\""))
{
insUpdPrmListOzel.Add(insUpdPrm);
}
}
template = template + "string sql = @\"UPDATE " + tableName.ToUpperInvariant() +
" SET " + string.Join(", ", updateStrListOzel) + @"
WHERE " + whereObj.ColumnName + " = @" + whereObj.ColumnName + "\";" + @"
List<DbParameter> parameters = new List<DbParameter>();
";
string dbWhereParemeter = "";
if (whereObj.dbType.Equals(DbType.String))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName +
"\", DbType.String, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Decimal))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName +
"\", DbType.Decimal, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Int64))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName +
"\", DbType.Int64, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Int32))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName +
"\", DbType.Int32, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Int16))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName +
"\", DbType.Int16, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.DateTime))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName +
"\", DbType.DateTime, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Binary))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" +
whereObj.ColumnName + "\", DbType.Binary, " + whereObj.ColumnName + "));" + newLine;
}
template = template + string.Join(newLine, insUpdPrmListOzel) + newLine;
template = template + dbWhereParemeter + @"
using (Database db = new Database())
{
return db.ExecuteNonQuery(sql, parameters.ToArray()) > 0;
}
}" + newLine;
}
foreach (var whereObj in whereList)
{
template = template + @"
public bool " + tableName + "DeleteBy" + whereObj.ColumnName + "(" +
whereObj.dbType.ToString() + " " + whereObj.ColumnName + @")
{
";
template = template + "string sql = @\"DELETE FROM " + tableName.ToUpperInvariant() +
" WHERE " + whereObj.ColumnName + " = @" + whereObj.ColumnName + "\";" + @"
List<DbParameter> parameters = new List<DbParameter>();
";
string dbWhereParemeter = "";
if (whereObj.dbType.Equals(DbType.String))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName +
"\", DbType.String, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Decimal))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName +
"\", DbType.Decimal, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Int64))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName +
"\", DbType.Int64, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Int32))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName +
"\", DbType.Int32, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Int16))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName +
"\", DbType.Int16, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.DateTime))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" + whereObj.ColumnName +
"\", DbType.DateTime, " + whereObj.ColumnName + "));" + newLine;
}
if (whereObj.dbType.Equals(DbType.Binary))
{
dbWhereParemeter = dbWhereParemeter + "parameters.Add(new DbParameter(\"" +
whereObj.ColumnName + "\", DbType.Binary, " + whereObj.ColumnName + "));" + newLine;
}
template = template + dbWhereParemeter + @"
using (Database db = new Database())
{
return db.ExecuteNonQuery(sql, parameters.ToArray()) > 0;
}
}" + newLine;
}
template = template + @"
}
}";
return(template);
}
