/// <summary>
/// Creates the connection object and opens it with the specified connection string.
/// </summary>
private void OpenConnection(ref ADODB.Connection oConn)
{
ConnectionString = Config.Setting(ConnectionName);
if (Config.bSetting(ConnectionName + ".Encrypted"))
{
ConnectionString = CSAAWeb.Cryptor.Decrypt(ConnectionString, CSAAWeb.Constants.CS_STRING);
}
if (ConnectionString.ToLower().IndexOf("dsn=") == -1 &&
ConnectionString.ToLower().IndexOf("provider=") == -1)
{
ConnectionString = "Provider=SQLOLEDB.1;" + ConnectionString;
}
oConn = new ADODB.ConnectionClass();
oConn.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, oConn, new object[] { ConnectionString });
}
private bool IsColumnNullable(string name, string column)
{
if (radioSql.Checked || radioSql2005.Checked)
{
int tableid = Convert.ToInt32(DbSession.Default.FromSql("select id from sysobjects where [name] = '" + name + "'").ToScalar());
int isnullable = Convert.ToInt32(DbSession.Default.FromSql("select isnullable from syscolumns where [name] = '" + column + "' and id = " + tableid).ToScalar());
return isnullable == 1;
}
else if (radioOracle.Checked)
{
string isnullable = DbSession.Default.FromSql("select NULLABLE,COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE from user_tab_columns "
+ " where upper(table_name) ='" + name.ToUpper() + "' and upper(column_name) = '" + column.ToUpper() + "'").ToScalar<string>();
return isnullable.ToUpper() == "Y";
}
else if (radioSQLite.Checked)
{
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table = conn.GetSchema("COLUMNS");
conn.Close();
foreach (DataRow row in table.Rows)
{
if (row["TABLE_NAME"].ToString() == name && row["COLUMN_NAME"].ToString() == column)
{
return Convert.ToBoolean(row["IS_NULLABLE"].ToString());
}
}
}
else if (radioMySQL.Checked)
{
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table = conn.GetSchema("COLUMNS");
conn.Close();
foreach (DataRow row in table.Rows)
{
if (row["TABLE_NAME"].ToString() == name && row["COLUMN_NAME"].ToString() == column)
{
return row["IS_NULLABLE"].ToString().ToUpper() == "YES";
}
}
}
else if (radioAccess.Checked)
{
string connStr = txtConnStr.Text;
ADODB.ConnectionClass conn = new ADODB.ConnectionClass();
conn.Open(connStr);
ADODB.Recordset rs = conn.GetType().InvokeMember("OpenSchema", BindingFlags.InvokeMethod, null, conn, new object[] { ADODB.SchemaEnum.adSchemaColumns }) as ADODB.Recordset;
rs.Filter = "TABLE_NAME='" + name + "'";
while (!rs.EOF)
{
if (rs.Fields["COLUMN_NAME"].Value.ToString() == column)
{
bool result = Convert.ToBoolean(rs.Fields["IS_NULLABLE"].Value);
rs.Close();
conn.Close();
return result;
}
rs.MoveNext();
}
}
return false;
}
private bool IsColumnReadOnly(string name, string column)
{
if (radioSql.Checked || radioSql2005.Checked)
{
int tableid = Convert.ToInt32(DbSession.Default.FromSql("select id from sysobjects where [name] = '" + name + "'").ToScalar());
byte status = Convert.ToByte(DbSession.Default.FromSql("select status from syscolumns where [name] = '" + column + "' and id =" + tableid).ToScalar());
return status == 128;
}
else if (radioAccess.Checked)
{
string connStr = txtConnStr.Text;
ADODB.ConnectionClass conn = new ADODB.ConnectionClass();
conn.Open(connStr);
ADODB.Recordset rs = conn.GetType().InvokeMember("OpenSchema", BindingFlags.InvokeMethod, null, conn, new object[] { ADODB.SchemaEnum.adSchemaColumns }) as ADODB.Recordset;
rs.Filter = "TABLE_NAME='" + name + "'";
while (!rs.EOF)
{
if (rs.Fields["COLUMN_NAME"].Value.ToString() == column && ((int)rs.Fields["DATA_TYPE"].Value) == 3 && Convert.ToByte(rs.Fields["COLUMN_FLAGS"].Value) == 90)
{
rs.Close();
conn.Close();
return true;
}
rs.MoveNext();
}
}
else if (radioSQLite.Checked)
{
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table = conn.GetSchema("COLUMNS");
conn.Close();
foreach (DataRow row in table.Rows)
{
if (row["TABLE_NAME"].ToString() == name && row["COLUMN_NAME"].ToString() == column && row["DATA_TYPE"].ToString() == "INTEGER")
{
return true;
}
}
}
else if (radioMySQL.Checked)
{
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table = conn.GetSchema("COLUMNS");
conn.Close();
foreach (DataRow row in table.Rows)
{
if (row["TABLE_NAME"].ToString() == name && row["COLUMN_NAME"].ToString() == column && row["EXTRA"].ToString().ToUpper() == "AUTO_INCREMENT")
{
return true;
}
}
}
return false;
}
private bool IsColumnPrimaryKey(string name, string column)
{
if (radioSql.Checked || radioSql2005.Checked)
{
int tableid = Convert.ToInt32(DbSession.Default.FromSql("select id from sysobjects where [name] = '" + name + "'").ToScalar());
DataSet ds = DbSession.Default.FromSql("select a.name FROM syscolumns a inner join sysobjects d on a.id=d.id and d.xtype='U' "
+ " and d.name<>'dtproperties' where (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND "
+ " (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) "
+ " AND (name = a.name))))))) AND (xtype = 'PK'))>0 and d.id = " + tableid).ToDataSet();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i][0].ToString() == column)
{
return true;
}
}
}
else if (radioOracle.Checked)
{
DataSet ds = DbSession.Default.FromSql("select col.column_name from user_constraints con,user_cons_columns col where con.constraint_name = "
+ " col.constraint_name and con.constraint_type='P' and upper(col.table_name) = '" + name.ToUpper() + "'").ToDataSet();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i][0].ToString() == column)
{
return true;
}
}
}
else if (radioAccess.Checked)
{
string connStr = txtConnStr.Text;
ADODB.ConnectionClass conn = new ADODB.ConnectionClass();
conn.Open(connStr);
ADODB.Recordset rs = conn.GetType().InvokeMember("OpenSchema", BindingFlags.InvokeMethod, null, conn, new object[] { ADODB.SchemaEnum.adSchemaPrimaryKeys }) as ADODB.Recordset;
rs.Filter = "TABLE_NAME='" + name + "'";
while (!rs.EOF)
{
if (rs.Fields["COLUMN_NAME"].Value.ToString() == column)
{
rs.Close();
conn.Close();
return true;
}
rs.MoveNext();
}
}
else if (radioSQLite.Checked)
{
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table = conn.GetSchema("COLUMNS");
conn.Close();
foreach (DataRow row in table.Rows)
{
if (row["TABLE_NAME"].ToString() == name && row["COLUMN_NAME"].ToString() == column)
{
return Convert.ToBoolean(row["PRIMARY_KEY"].ToString());
}
}
}
else if (radioMySQL.Checked)
{
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table = conn.GetSchema("COLUMNS");
conn.Close();
foreach (DataRow row in table.Rows)
{
if (row["TABLE_NAME"].ToString() == name && row["COLUMN_NAME"].ToString() == column)
{
return row["COLUMN_KEY"].ToString().ToUpper() == "PRI";
}
}
}
return false;
}
private void btnConnect_Click(object sender, EventArgs e)
{
if (txtConnStr.Text.Trim().Length == 0)
{
MessageBox.Show("连接字符串不能为空!");
return;
}
if (btnConnect.Text == "断开服务器连接")
{
chkCreateAssembly.Checked = false;
chkCreateAssembly_CheckedChanged(sender, e);
EnableGenEntity(false);
return;
}
RefreshConnectionStringAutoComplete();
DataSet dsTables = null;
DataSet dsViews = null;
try
{
if (radioSql.Checked || radioSql2005.Checked)
{
DbProvider dbProvider = new SqlServerProvider(txtConnStr.Text);
DbSession.SetDefault(dbProvider);
if (radioSql2005.Checked)
{
dsTables = DbSession.Default.FromSql("select [name] from sysobjects where xtype = 'U' and [name] <> 'sysdiagrams' order by [name]").ToDataSet();
}
else
{
dsTables = DbSession.Default.FromSql("select [name] from sysobjects where xtype = 'U' and status > 0 order by [name]").ToDataSet();
}
foreach (DataRow row in dsTables.Tables[0].Rows)
{
tables.Items.Add(row["Name"].ToString());
}
if (radioSql2005.Checked)
{
dsViews = DbSession.Default.FromSql("select [name] from sysobjects where xtype = 'V' order by [name]").ToDataSet();
}
else
{
dsViews = DbSession.Default.FromSql("select [name] from sysobjects where xtype = 'V' and status > 0 order by [name]").ToDataSet();
}
foreach (DataRow row in dsViews.Tables[0].Rows)
{
views.Items.Add(row["Name"].ToString());
}
}
else if (radioOracle.Checked)
{
DbProvider dbProvider = new OracleProvider(txtConnStr.Text);
DbSession.SetDefault(dbProvider);
dsTables = DbSession.Default.FromSql("select table_name Name from user_tables").ToDataSet();
foreach (DataRow row in dsTables.Tables[0].Rows)
{
tables.Items.Add(row["Name"].ToString());
}
dsViews = DbSession.Default.FromSql("select view_name Name from user_views").ToDataSet();
foreach (DataRow row in dsViews.Tables[0].Rows)
{
views.Items.Add(row["Name"].ToString());
}
}
else if (radioAccess.Checked)
{
DbProvider dbProvider = new MsAccessProvider(txtConnStr.Text);
DbSession.SetDefault(dbProvider);
string connStr = txtConnStr.Text;
ADODB.ConnectionClass conn = new ADODB.ConnectionClass();
conn.Open(connStr);
ADODB.Recordset rsTables = conn.GetType().InvokeMember("OpenSchema", BindingFlags.InvokeMethod, null, conn, new object[] { ADODB.SchemaEnum.adSchemaTables }) as ADODB.Recordset;
ADODB.Recordset rsViews = conn.GetType().InvokeMember("OpenSchema", BindingFlags.InvokeMethod, null, conn, new object[] { ADODB.SchemaEnum.adSchemaViews }) as ADODB.Recordset;
while (!rsViews.EOF)
{
if (rsTables.Fields["TABLE_TYPE"].Value.ToString() == "VIEW")
{
views.Items.Add(rsViews.Fields["TABLE_NAME"].Value.ToString());
}
rsViews.MoveNext();
}
while (!rsTables.EOF)
{
if (rsTables.Fields["TABLE_TYPE"].Value.ToString() == "TABLE")
{
tables.Items.Add(rsTables.Fields["TABLE_NAME"].Value.ToString());
}
rsTables.MoveNext();
}
rsTables.Close();
rsViews.Close();
conn.Close();
}
else if (radioSQLite.Checked)
{
DbProvider dbProvider = DbProviderFactory.CreateDbProvider(DbProviderType.SQLite, txtConnStr.Text);
DbSession.SetDefault(dbProvider);
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table1 = conn.GetSchema("TABLES");
DataTable table2 = conn.GetSchema("VIEWS");
conn.Close();
foreach (DataRow row in table1.Rows)
{
if (row["TABLE_TYPE"].ToString().ToUpper() == "TABLE")
{
tables.Items.Add(row["TABLE_NAME"].ToString());
}
}
foreach (DataRow row in table2.Rows)
{
views.Items.Add(row["TABLE_NAME"].ToString());
}
}
else if (radioMySQL.Checked)
{
DbProvider dbProvider = DbProviderFactory.CreateDbProvider(DbProviderType.MySql, txtConnStr.Text);
DbSession.SetDefault(dbProvider);
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table1 = conn.GetSchema("TABLES");
DataTable table2 = conn.GetSchema("VIEWS");
conn.Close();
foreach (DataRow row in table1.Rows)
{
if (row["TABLE_TYPE"].ToString() == "BASE TABLE")
{
tables.Items.Add(row["TABLE_NAME"].ToString());
}
}
foreach (DataRow row in table2.Rows)
{
views.Items.Add(row["TABLE_NAME"].ToString());
}
}
EnableGenEntity(true);
}
catch (Exception ex)
{
EnableGenEntity(false);
MessageBox.Show(ex.Message, "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
