public static void modifyTable(string connName, TableDef tableDef)
{
DatabaseAdmin da = DatabaseAdmin.getInstance(connName);
da.modifyTable(tableDef);
}
internal DataSet getSourceDataSet(string where, string orderBy, PaginationInfo pi)
{
string connName = _schema.ConnectionName;
string tableName = _schema.TableName;
Database db = dbAdmin.Database;
List <string> pks = dbAdmin.GetPrimaryKeys(tableName);
DbCommand cmd = null;
if (!dbAdmin.ExistsSp(DataSourceConst.PaginationSpName))
{
dbAdmin.modifyProc(DataSourceConst.PaginationSpName, DataSourceConst.PaginationSpText);
}
if (pks.Count() == 1)
{
pi.isStoreProcessPagination = true;
cmd = db.GetStoredProcCommand(DataSourceConst.PaginationSpName);
cmd.CommandType = CommandType.StoredProcedure;
db.AddInParameter(cmd, "@TableName", DbType.String, tableName);
db.AddInParameter(cmd, "@PrimaryKey", DbType.String, pks[0]);
db.AddInParameter(cmd, "@PageNo", DbType.Int32, pi.page);
db.AddInParameter(cmd, "@PageSize", DbType.Int32, pi.pageSize);
db.AddInParameter(cmd, "@Where", DbType.String, where);
db.AddOutParameter(cmd, "@PageCount", DbType.Int32, sizeof(Int32));
db.AddOutParameter(cmd, "@Total", DbType.Int32, sizeof(Int32));
}
else
{
pi.isStoreProcessPagination = false;
StringBuilder sbSql = new StringBuilder();
sbSql.Append("Select * From ");
sbSql.Append(tableName);
if (!string.IsNullOrEmpty(where))
{
sbSql.Append(" Where ");
sbSql.Append(where);
}
if (!string.IsNullOrEmpty(orderBy))
{
sbSql.Append(" Order By ");
sbSql.Append(orderBy);
}
cmd = db.GetSqlStringCommand(sbSql.ToString());
}
DataSet ds = db.ExecuteDataSet(cmd);
TableDef tableDef = dbAdmin.getTableDef(tableName);
DataTable table = ds.Tables[0];
if (pks.Count > 0)
{
DataColumn[] primaryKey = new DataColumn[2];
for (int i = 0; i < pks.Count; i++)
{
primaryKey[i] = table.Columns[pks[i]];
}
table.PrimaryKey = primaryKey;
}
foreach (FieldDef fieldDef in tableDef.FieldDefs)
{
DataColumn col = table.Columns[fieldDef.Name];
if (!string.IsNullOrEmpty(fieldDef.Alias))
{
col.Caption = fieldDef.Alias;
}
if (fieldDef.IsIdentity)
{
col.AutoIncrement = true;
col.ReadOnly = true;
}
}
if (pi.isStoreProcessPagination)
{
pi.pageCount = (int)cmd.Parameters["@PageCount"].Value;
pi.total = (int)cmd.Parameters["@Total"].Value;
}
else
{
pi.total = table.Rows.Count;
pi.pageCount = pi.total / pi.pageSize;
if (pi.total % pi.pageSize != 0)
{
pi.pageCount++;
}
}
return(ds);
}
private void applyTableDef(DataSet dataSet)
{
foreach (DataTable table in dataSet.Tables)
{
string tableName = table.TableName;
List <string> pks = dbAdmin.GetPrimaryKeys(tableName);
var tbName = tableName.Trim('[', ']');
TableDef tbDef = dbAdmin.getTableDef(tbName);
if (tbDef == null)
{
break;
}
List <FieldDef> fieldDefs = tbDef.FieldDefs;
for (int i = 0; i < fieldDefs.Count; i++)
{
FieldDef fieldDef = fieldDefs[i];
string colName = fieldDef.Name;
if (table.Columns.Contains(colName))
{
DataColumn col = table.Columns[colName];
col.Caption = colName;
if (!string.IsNullOrEmpty(fieldDef.Title))
{
col.Caption = fieldDef.Title;
}
//if (string.IsNullOrEmpty(fieldDef.DefaultValue))
// col.DefaultValue = fieldDef.DefaultValue;
col.AutoIncrement = fieldDef.IsIdentity;
//if (!string.IsNullOrEmpty(fieldDef.Description))
col.ExtendedProperties[DataSourceConst.ExProDescription] = fieldDef.Description;
DbType dbType;
try
{
dbType = dbAdmin.getDbType(fieldDef.Type);
}
catch
{
throw new Exception(string.Format(Lang.unknowDbType, tableName, colName));
}
if (!col.ExtendedProperties.ContainsKey(DataSourceConst.ExProDbType))
{
col.ExtendedProperties.Add(DataSourceConst.ExProDbType, dbType);
}
else
{
col.ExtendedProperties[DataSourceConst.ExProDbType] = dbType;
}
}
}
if (pks != null && pks.Count > 0)
{
DataColumn[] cols = new DataColumn[pks.Count];
for (int i = 0; i < pks.Count; i++)
{
string colName = pks[i];
if (table.Columns.Contains(colName))
{
cols[i] = table.Columns[colName];
}
}
table.PrimaryKey = cols;
}
}
}
internal static DataSet getSourceDataSet(string dsName, string where, string orderBy, PaginationInfo pi)
{
if (string.IsNullOrEmpty(dsName))
{
throw new XException(Lang.DataSourceNameIsNull);
}
string connName = "";
string tableName = dsName;
string[] namePath = dsName.Split('.');
if (namePath.Length > 1)
{
connName = namePath[0];
tableName = namePath[1];
}
DatabaseAdmin dba = DatabaseAdmin.getInstance(connName);
Database db = dba.Database;
List <string> pks = dba.GetPrimaryKeys(tableName);
DbCommand cmd = null;
if (!dba.ExistsSp(DataSourceConst.PaginationSpName))
{
dba.modifyProc(DataSourceConst.PaginationSpName, DataSourceConst.PaginationSpText);
}
if (pks.Count() == 1)
{
pi.isStoreProcessPagination = true;
cmd = db.GetStoredProcCommand(DataSourceConst.PaginationSpName);
cmd.CommandType = CommandType.StoredProcedure;
db.AddInParameter(cmd, "@TableName", DbType.String, tableName);
db.AddInParameter(cmd, "@PrimaryKey", DbType.String, pks[0]);
db.AddInParameter(cmd, "@PageNo", DbType.Int32, pi.page);
db.AddInParameter(cmd, "@PageSize", DbType.Int32, pi.pageSize);
db.AddInParameter(cmd, "@Where", DbType.String, where);
db.AddOutParameter(cmd, "@PageCount", DbType.Int32, sizeof(Int32));
db.AddOutParameter(cmd, "@Total", DbType.Int32, sizeof(Int32));
}
else
{
pi.isStoreProcessPagination = false;
StringBuilder sbSql = new StringBuilder();
sbSql.Append("Select * From ");
sbSql.Append(tableName);
if (!string.IsNullOrEmpty(where))
{
sbSql.Append(" Where ");
sbSql.Append(where);
}
if (!string.IsNullOrEmpty(orderBy))
{
sbSql.Append(" Order By ");
sbSql.Append(orderBy);
}
cmd = db.GetSqlStringCommand(sbSql.ToString());
}
DataSet ds = db.ExecuteDataSet(cmd);
TableDef tableDef = dba.getTableDef(tableName);
DataTable table = ds.Tables[0];
foreach (FieldDef fieldDef in tableDef.FieldDefs)
{
if (string.IsNullOrEmpty(fieldDef.Alias))
{
continue;
}
table.Columns[fieldDef.Name].Caption = fieldDef.Alias;
}
if (pi.isStoreProcessPagination)
{
pi.pageCount = (int)cmd.Parameters["@PageCount"].Value;
pi.total = (int)cmd.Parameters["@Total"].Value;
}
else
{
pi.total = table.Rows.Count;
pi.pageCount = pi.total / pi.pageSize;
if (pi.total % pi.pageSize != 0)
{
pi.pageCount++;
}
}
return(ds);
}