public DataTable GetKeyName(string DbName, string TableName)
{
DataTable dataTable = this.CreateColumnTable();
List <ColumnInfo> columnInfoList = this.GetColumnInfoList(DbName, TableName);
DataTable columnInfoDt = CodeCommon.GetColumnInfoDt(columnInfoList);
DataRow[] array = columnInfoDt.Select(" isPK='√' or IsIdentity='√' ");
DataRow[] array2 = array;
for (int i = 0; i < array2.Length; i++)
{
DataRow dataRow = array2[i];
DataRow dataRow2 = dataTable.NewRow();
dataRow2["colorder"] = dataRow["colorder"];
dataRow2["ColumnName"] = dataRow["ColumnName"];
dataRow2["TypeName"] = dataRow["TypeName"];
dataRow2["Length"] = dataRow["Length"];
dataRow2["Preci"] = dataRow["Preci"];
dataRow2["Scale"] = dataRow["Scale"];
dataRow2["IsIdentity"] = dataRow["IsIdentity"];
dataRow2["isPK"] = dataRow["isPK"];
dataRow2["cisNull"] = dataRow["cisNull"];
dataRow2["defaultVal"] = dataRow["defaultVal"].ToString().Replace("(", "").Replace(")", "");
dataRow2["deText"] = dataRow["deText"];
dataTable.Rows.Add(dataRow2);
}
return(dataTable);
}
/// <summary>
/// 得到主键的对象信息
/// </summary>
/// <returns></returns>
private List <ColumnInfo> GetKeyFields()
{
List <ColumnInfo> columnInfoList = this.dbobj.GetColumnInfoList(this.dbname, this.tablename);
DataTable columnInfoDt = CodeCommon.GetColumnInfoDt(columnInfoList);
StringPlus stringPlus = new StringPlus();
foreach (object current in this.list_KeyField.Items)
{
stringPlus.Append("'" + current.ToString() + "',");
}
stringPlus.DelLastComma();
if (columnInfoDt != null)
{
DataRow[] array;
if (stringPlus.Value.Length > 0)
{
array = columnInfoDt.Select("ColumnName in (" + stringPlus.Value + ")", "colorder asc");
}
else
{
array = columnInfoDt.Select();
}
List <ColumnInfo> list = new List <ColumnInfo>();
DataRow[] array2 = array;
for (int i = 0; i < array2.Length; i++)
{
DataRow dataRow = array2[i];
string columnOrder = dataRow["Colorder"].ToString();
string columnName = dataRow["ColumnName"].ToString();
string typeName = dataRow["TypeName"].ToString();
string a = dataRow["IsIdentity"].ToString();
string a2 = dataRow["IsPK"].ToString();
string length = dataRow["Length"].ToString();
string precision = dataRow["Preci"].ToString();
string scale = dataRow["Scale"].ToString();
string a3 = dataRow["cisNull"].ToString();
string defaultVal = dataRow["DefaultVal"].ToString();
string description = dataRow["DeText"].ToString();
list.Add(new ColumnInfo
{
ColumnOrder = columnOrder,
ColumnName = columnName,
TypeName = typeName,
IsIdentity = a == "√",
IsPrimaryKey = a2 == "√",
Length = length,
Precision = precision,
Scale = scale,
Nullable = a3 == "√",
DefaultVal = defaultVal,
Description = description
});
}
return(list);
}
return(null);
}
public DataTable GetKeyName(string DbName, string TableName)
{
DataTable table = this.CreateColumnTable();
foreach (DataRow row in CodeCommon.GetColumnInfoDt(this.GetColumnInfoList(DbName, TableName)).Select(" isPK='√' or IsIdentity='√' "))
{
DataRow row2 = table.NewRow();
row2["colorder"] = row["colorder"];
row2["ColumnName"] = row["ColumnName"];
row2["TypeName"] = row["TypeName"];
row2["Length"] = row["Length"];
row2["Preci"] = row["Preci"];
row2["Scale"] = row["Scale"];
row2["IsIdentity"] = row["IsIdentity"];
row2["isPK"] = row["isPK"];
row2["cisNull"] = row["cisNull"];
row2["defaultVal"] = row["defaultVal"];
row2["deText"] = row["deText"];
table.Rows.Add(row2);
}
return(table);
}
/// <summary>
/// 得到数据库里表的主键
/// </summary>
/// <param name="dbName">库</param>
/// <param name="tableName">表</param>
/// <returns></returns>
public DataTable GetKeyName(string dbName, string tableName)
{
DataTable dtkey = CreateColumnTable();
//DataTable dt = GetColumnInfoList(DbName, TableName);
List <ColumnInfo> collist = GetColumnInfoList(dbName, tableName);
DataTable dt = CodeCommon.GetColumnInfoDt(collist);
DataRow[] rows = dt.Select(" isPK='√' or IsIdentity='√' ");
foreach (DataRow row in rows)
{
DataRow nrow = dtkey.NewRow();
nrow["colorder"] = row["colorder"];
nrow["ColumnName"] = row["ColumnName"];
nrow["TypeName"] = row["TypeName"];
nrow["Length"] = row["Length"];
nrow["Preci"] = row["Preci"];
nrow["Scale"] = row["Scale"];
nrow["IsIdentity"] = row["IsIdentity"];
nrow["isPK"] = row["isPK"];
nrow["IsNull"] = row["IsNull"];
nrow["defaultVal"] = row["defaultVal"];
nrow["deText"] = row["deText"];
dtkey.Rows.Add(nrow);
}
return(dtkey);
#region
//StringBuilder strSql = new StringBuilder();
//strSql.Append("select * from ");
//strSql.Append("( ");
//strSql.Append("SELECT ");
//strSql.Append("colorder=C.column_id,");
//strSql.Append("ColumnName=C.name,");
//strSql.Append("TypeName=T.name, ");
//strSql.Append("Length=C.max_length, ");
//strSql.Append("Preci=C.precision, ");
//strSql.Append("Scale=C.scale, ");
//strSql.Append("IsIdentity=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,");
//strSql.Append("isPK=ISNULL(IDX.PrimaryKey,N''),");
//strSql.Append("Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END, ");
//strSql.Append("IndexName=ISNULL(IDX.IndexName,N''), ");
//strSql.Append("IndexSort=ISNULL(IDX.Sort,N''), ");
//strSql.Append("Create_Date=O.Create_Date, ");
//strSql.Append("Modify_Date=O.Modify_date, ");
//strSql.Append("IsNull=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END, ");
//strSql.Append("defaultVal=ISNULL(D.definition,N''), ");
//strSql.Append("deText=ISNULL(PFD.[value],N'') ");
//strSql.Append("FROM sys.columns C ");
//strSql.Append("INNER JOIN sys.objects O ");
//strSql.Append("ON C.[object_id]=O.[object_id] ");
//strSql.Append("AND O.type='U' ");
//strSql.Append("AND O.is_ms_shipped=0 ");
//strSql.Append("INNER JOIN sys.types T ");
//strSql.Append("ON C.user_type_id=T.user_type_id ");
//strSql.Append("LEFT JOIN sys.default_constraints D ");
//strSql.Append("ON C.[object_id]=D.parent_object_id ");
//strSql.Append("AND C.column_id=D.parent_column_id ");
//strSql.Append("AND C.default_object_id=D.[object_id] ");
//strSql.Append("LEFT JOIN sys.extended_properties PFD ");
//strSql.Append("ON PFD.class=1 ");
//strSql.Append("AND C.[object_id]=PFD.major_id ");
//strSql.Append("AND C.column_id=PFD.minor_id ");
////strSql.Append("--AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述) ");
//strSql.Append("LEFT JOIN sys.extended_properties PTB ");
//strSql.Append("ON PTB.class=1 ");
//strSql.Append("AND PTB.minor_id=0 ");
//strSql.Append("AND C.[object_id]=PTB.major_id ");
////strSql.Append("-- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) ");
//strSql.Append("LEFT JOIN ");// -- 索引及主键信息
//strSql.Append("( ");
//strSql.Append("SELECT ");
//strSql.Append("IDXC.[object_id], ");
//strSql.Append("IDXC.column_id, ");
//strSql.Append("Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending') ");
//strSql.Append("WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END, ");
//strSql.Append("PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END, ");
//strSql.Append("IndexName=IDX.Name ");
//strSql.Append("FROM sys.indexes IDX ");
//strSql.Append("INNER JOIN sys.index_columns IDXC ");
//strSql.Append("ON IDX.[object_id]=IDXC.[object_id] ");
//strSql.Append("AND IDX.index_id=IDXC.index_id ");
//strSql.Append("LEFT JOIN sys.key_constraints KC ");
//strSql.Append("ON IDX.[object_id]=KC.[parent_object_id] ");
//strSql.Append("AND IDX.index_id=KC.unique_index_id ");
//strSql.Append("INNER JOIN ");// 对于一个列包含多个索引的情况,只显示第1个索引信息
//strSql.Append("( ");
//strSql.Append("SELECT [object_id], Column_id, index_id=MIN(index_id) ");
//strSql.Append("FROM sys.index_columns ");
//strSql.Append("GROUP BY [object_id], Column_id ");
//strSql.Append(") IDXCUQ ");
//strSql.Append("ON IDXC.[object_id]=IDXCUQ.[object_id] ");
//strSql.Append("AND IDXC.Column_id=IDXCUQ.Column_id ");
//strSql.Append("AND IDXC.index_id=IDXCUQ.index_id ");
//strSql.Append(") IDX ");
//strSql.Append("ON C.[object_id]=IDX.[object_id] ");
//strSql.Append("AND C.column_id=IDX.column_id ");
//strSql.Append("WHERE O.name=N'" + TableName + "' ");
//strSql.Append(") Keyname ");
//strSql.Append(" where isPK='√' or (IsIdentity='√' or colorder=1)");
//return Query(DbName, strSql.ToString()).Tables[0];
#endregion
}
public void CreateTabScript(string dbname, string tablename, string filename, ProgressBar progressBar)
{
StreamWriter streamWriter = new StreamWriter(filename, true, Encoding.Default);
this.dbobj.DbConnectStr = this._dbconnectStr;
List <ColumnInfo> columnInfoList = this.dbobj.GetColumnInfoList(dbname, tablename);
DataTable columnInfoDt = CodeCommon.GetColumnInfoDt(columnInfoList);
StringPlus stringPlus = new StringPlus();
new StringPlus();
Hashtable hashtable = new Hashtable();
StringPlus stringPlus2 = new StringPlus();
stringPlus.AppendLine("");
stringPlus.Append(this.dbobj.GetObjectInfo(dbname, tablename));
stringPlus.AppendLine(";");
if (columnInfoDt != null)
{
DataRow[] array;
if (this.Fieldlist.Count > 0)
{
array = columnInfoDt.Select("ColumnName in (" + this.Fields + ")", "colorder asc");
}
else
{
array = columnInfoDt.Select();
}
DataRow[] array2 = array;
for (int i = 0; i < array2.Length; i++)
{
DataRow dataRow = array2[i];
string text = dataRow["ColumnName"].ToString();
string value = dataRow["TypeName"].ToString();
string a = dataRow["IsIdentity"].ToString();
if (!(a == "√"))
{
hashtable.Add(text, value);
stringPlus2.Append(text + ",");
}
}
}
stringPlus2.DelLastComma();
streamWriter.Write(stringPlus.Value);
DataTable tabData = this.dbobj.GetTabData(dbname, tablename);
if (tabData != null)
{
int num = 0;
progressBar.Maximum = tabData.Rows.Count;
foreach (DataRow dataRow2 in tabData.Rows)
{
num++;
progressBar.Value = num;
StringPlus stringPlus3 = new StringPlus();
StringPlus stringPlus4 = new StringPlus();
StringPlus stringPlus5 = new StringPlus();
string[] array3 = stringPlus2.Value.Split(new char[]
{
','
});
string[] array4 = array3;
int j = 0;
while (j < array4.Length)
{
string text2 = array4[j];
string text3 = text2;
string text4 = "";
foreach (DictionaryEntry dictionaryEntry in hashtable)
{
if (dictionaryEntry.Key.ToString() == text3)
{
text4 = dictionaryEntry.Value.ToString();
}
}
string a2;
if ((a2 = text4) == null)
{
goto IL_2CF;
}
string text5;
if (!(a2 == "binary"))
{
if (!(a2 == "bit"))
{
goto IL_2CF;
}
text5 = ((dataRow2[text3].ToString().ToLower() == "true") ? "1" : "0");
}
else
{
byte[] bytes = (byte[])dataRow2[text3];
text5 = CodeCommon.ToHexString(bytes);
}
IL_2E4:
if (text5 != "")
{
if (CodeCommon.IsAddMark(text4))
{
stringPlus5.Append("'" + text5.Replace("'", "''") + "',");
}
else
{
stringPlus5.Append(text5 + ",");
}
stringPlus4.Append("[" + text3 + "],");
}
j++;
continue;
IL_2CF:
text5 = dataRow2[text3].ToString().Trim();
goto IL_2E4;
}
stringPlus4.DelLastComma();
stringPlus5.DelLastComma();
stringPlus3.Append("INSERT [" + tablename + "] (");
stringPlus3.Append(stringPlus4.Value);
stringPlus3.Append(") VALUES ( ");
stringPlus3.Append(stringPlus5.Value);
stringPlus3.AppendLine(");");
streamWriter.Write(stringPlus3.Value);
}
}
streamWriter.Flush();
streamWriter.Close();
}
/// <summary>
/// 生成数据库表创建脚本
/// </summary>
/// <returns></returns>
public string CreateTabScript(string dbname, string tablename)
{
dbobj.DbConnectStr = _dbconnectStr;
List <ColumnInfo> collist = dbobj.GetColumnInfoList(dbname, tablename);
DataTable dt = CodeCommon.GetColumnInfoDt(collist);
StringPlus strclass = new StringPlus();
string PKfild = ""; //主键字段
StringPlus ColdefaVal = new StringPlus(); //字段的默认值列表
Hashtable FildtabList = new Hashtable(); //字段列表
StringPlus FildList = new StringPlus(); //字段列表
//开始创建表
strclass.AppendLine("");
strclass.AppendLine("CREATE TABLE \"" + tablename + "\" (");
if (dt != null)
{
DataRow[] dtrows;
dtrows = Fieldlist.Count > 0 ? dt.Select("ColumnName in (" + Fields + ")", "colorder asc") : dt.Select();
foreach (DataRow row in dt.Rows)
{
string columnName = row["ColumnName"].ToString();
string columnType = row["TypeName"].ToString();
string Length = row["Length"].ToString();
string Preci = row["Preci"].ToString();
string Scale = row["Scale"].ToString();
string ispk = row["isPK"].ToString();
string isnull = row["isNull"].ToString();
string defaultVal = row["defaultVal"].ToString();
strclass.Append("\"" + columnName + "\" " + columnType + " ");
switch (columnType.Trim())
{
case "CHAR":
case "VARCHAR2":
case "NCHAR":
case "NVARCHAR2":
strclass.Append(" (" + Length + ")");
break;
case "NUMBER":
strclass.Append(" (" + Preci + "," + Scale + ")");
break;
}
strclass.Append(isnull == "√" ? " NULL" : " NOT NULL");
if (defaultVal != "")
{
strclass.Append(" DEFAULT " + defaultVal);
}
strclass.AppendLine(",");
FildtabList.Add(columnName, columnType);
FildList.Append("\"" + columnName + "\",");
if (PKfild == "")
{
PKfild = columnName; //得到主键
}
}
}
strclass.DelLastComma();
FildList.DelLastComma();
strclass.AppendLine(")");
strclass.AppendLine("");
//获取数据
DataTable dtdata = dbobj.GetTabData(dbname, tablename);
if (dtdata != null)
{
foreach (DataRow row in dtdata.Rows) //循环表数据
{
StringPlus strfild = new StringPlus();
StringPlus strdata = new StringPlus();
string [] split = FildList.Value.Split(new Char [] { ',' });
foreach (string fild in split) //循环一行数据的各个字段
{
string colname = fild.Substring(1, fild.Length - 2);
string coltype = "";
foreach (DictionaryEntry myDe in FildtabList)
{
if (myDe.Key.ToString() == colname)
{
coltype = myDe.Value.ToString();
}
}
string strval = "";
switch (coltype)
{
case "BLOB":
{
byte[] bys = (byte[])row[colname];
strval = CodeCommon.ToHexString(bys);
}
break;
case "bit":
{
strval = (row[colname].ToString().ToLower() == "true")?"1":"0";
}
break;
default:
strval = row[colname].ToString().Trim();
break;
}
if (strval != "")
{
if (CodeCommon.IsAddMark(coltype))
{
strdata.Append("'" + strval + "',");
}
else
{
strdata.Append(strval + ",");
}
strfild.Append("" + colname + ",");
}
}
strfild.DelLastComma();
strdata.DelLastComma();
//导出数据INSERT语句
strclass.Append("INSERT \"" + tablename + "\" (");
strclass.Append(strfild.Value);
strclass.Append(") VALUES ( ");
strclass.Append(strdata.Value); //数据值
strclass.AppendLine(")");
}
}
return(strclass.Value);
}
public string CreateTabScript(string dbname, string tablename)
{
this.dbobj.DbConnectStr = this._dbconnectStr;
List <ColumnInfo> columnInfoList = this.dbobj.GetColumnInfoList(dbname, tablename);
DataTable columnInfoDt = CodeCommon.GetColumnInfoDt(columnInfoList);
StringPlus stringPlus = new StringPlus();
string a = "";
new StringPlus();
Hashtable hashtable = new Hashtable();
StringPlus stringPlus2 = new StringPlus();
stringPlus.AppendLine("");
stringPlus.AppendLine("CREATE TABLE [" + tablename + "] (");
if (columnInfoDt != null)
{
foreach (DataRow dataRow in columnInfoDt.Rows)
{
string text = dataRow["ColumnName"].ToString();
string text2 = dataRow["TypeName"].ToString();
string str = dataRow["Length"].ToString();
string text3 = dataRow["Preci"].ToString();
string text4 = dataRow["Scale"].ToString();
dataRow["isPK"].ToString();
string a2 = dataRow["cisNull"].ToString();
string text5 = dataRow["defaultVal"].ToString();
stringPlus.Append(string.Concat(new string[]
{
"[",
text,
"] [",
text2,
"] "
}));
string a3;
if ((a3 = text2.Trim()) != null)
{
if (!(a3 == "CHAR") && !(a3 == "VARCHAR2") && !(a3 == "NCHAR") && !(a3 == "NVARCHAR2"))
{
if (a3 == "NUMBER")
{
stringPlus.Append(string.Concat(new string[]
{
" (",
text3,
",",
text4,
")"
}));
}
}
else
{
stringPlus.Append(" (" + str + ")");
}
}
if (a2 == "√")
{
stringPlus.Append(" NULL");
}
else
{
stringPlus.Append(" NOT NULL");
}
if (text5 != "")
{
stringPlus.Append(" DEFAULT " + text5);
}
stringPlus.AppendLine(",");
hashtable.Add(text, text2);
stringPlus2.Append("[" + text + "],");
if (a == "")
{
a = text;
}
}
}
stringPlus.DelLastComma();
stringPlus2.DelLastComma();
stringPlus.AppendLine(")");
stringPlus.AppendLine("");
DataTable tabData = this.dbobj.GetTabData(dbname, tablename);
if (tabData != null)
{
foreach (DataRow dataRow2 in tabData.Rows)
{
StringPlus stringPlus3 = new StringPlus();
StringPlus stringPlus4 = new StringPlus();
string[] array = stringPlus2.Value.Split(new char[]
{
','
});
string[] array2 = array;
int i = 0;
while (i < array2.Length)
{
string text6 = array2[i];
string text7 = text6.Substring(1, text6.Length - 2);
string text8 = "";
foreach (DictionaryEntry dictionaryEntry in hashtable)
{
if (dictionaryEntry.Key.ToString() == text7)
{
text8 = dictionaryEntry.Value.ToString();
}
}
string a4;
if ((a4 = text8) == null)
{
goto IL_444;
}
string text9;
if (!(a4 == "BLOB"))
{
if (!(a4 == "bit"))
{
goto IL_444;
}
text9 = ((dataRow2[text7].ToString().ToLower() == "true") ? "1" : "0");
}
else
{
byte[] bytes = (byte[])dataRow2[text7];
text9 = CodeCommon.ToHexString(bytes);
}
IL_459:
if (text9 != "")
{
if (CodeCommon.IsAddMark(text8))
{
stringPlus4.Append("'" + text9.Replace("'", "''") + "',");
}
else
{
stringPlus4.Append(text9 + ",");
}
stringPlus3.Append("[" + text7 + "],");
}
i++;
continue;
IL_444:
text9 = dataRow2[text7].ToString().Trim();
goto IL_459;
}
stringPlus3.DelLastComma();
stringPlus4.DelLastComma();
stringPlus.Append("INSERT [" + tablename + "] (");
stringPlus.Append(stringPlus3.Value);
stringPlus.Append(") VALUES ( ");
stringPlus.Append(stringPlus4.Value);
stringPlus.AppendLine(")");
}
}
return(stringPlus.Value);
}