public sealed override List <BaseStoredProcedureInfo> GetStoredProceduresList()
{
List <BaseStoredProcedureInfo> info = new List <BaseStoredProcedureInfo>();
DataTable dt = this.GetConnection().GetSchema("PROCEDURES");
#if DEBUG
dt.TableName = "sp";
dt.WriteXml("sp.xml");
#endif
foreach (DataRow item in dt.Rows)
{
info.Add(new BaseStoredProcedureInfo()
{
ProcedureName = item["PROCEDURE_NAME"].ToString(),
ProcedureCategory = item["PROCEDURE_CAT"].ToString(),
ProcedureSchema = item["PROCEDURE_SCHEM"].ToString(),
Remarks = item["REMARKS"].ToString(),
SpecificName = item["SPECIFIC_NAME"].ToString(),
ProcedureType = (BaseSpType)int.Parse(item["PROCEDURE_TYPE"].ToString())
});
}
#region Fill with the cild column info in each sp
try
{
EfzConnection conn = this.GetConnection() as EfzConnection;
Debug.Assert(null != conn);
DataTable childDt = conn.GetSchema("PROCEDURECOLUMNS");
foreach (DataRow childRow in childDt.Rows)
{
string currentSpName = childRow["PROCEDURE_NAME"].ToString();
foreach (BaseStoredProcedureInfo childSpInfo in info)
{
if (childSpInfo.ProcedureName == currentSpName)
{
BaseColumnInfoInSP columnInfo = new BaseColumnInfoInSP()
{
COLUMN_NAME = childRow["COLUMN_NAME"].ToString(),
COLUMN_SIZE = int.Parse(childRow["COLUMN_SIZE"].ToString()),
DATA_TYPE = int.Parse(childRow["DATA_TYPE"].ToString()),
NULLABLE = bool.Parse(childRow["NULLABLE"].ToString()),
ORDINAL_POSITION = int.Parse(childRow["ORDINAL_POSITION"].ToString()),
PRECISION = int.Parse(childRow["PRECISION"].ToString()),
SCALE = int.Parse(childRow["SCALE"].ToString()),
TYPE_NAME = childRow["TYPE_NAME"].ToString()
};
break;
}
}
}
}
catch (Exception ee)
{
Debug.WriteLine(ee.Message);
}
#endregion
return(info);
}
/// <summary>
///
/// </summary>
/// <param name="databaseName">
/// In access ,this para is not used ever
/// Like SSCE ,Recommend use this base method with( no parameter)</param>
/// <returns></returns>
public override List <string> GetTableListInDatabase(string databaseName)
{
List <string> jieguo = new List <string>();
EfzDataAdapter reader = null;
string cmdStr = string.Empty;
EfzConnection myConn = (EfzConnection)baseConn;
using (EfzCommand cmd = new EfzCommand(cmdStr, myConn))
{
cmd.CommandTimeout = 10;
try
{
DataTable userTables;
userTables = myConn.GetSchema("Tables", new string[] { null, null, null, "TABLE" });
Debug.WriteLine("Table count " + userTables.Rows.Count);
for (int i = 0; i < userTables.Rows.Count; i++)
{
jieguo.Add(userTables.Rows[i]["Table_Name"].ToString());
Debug.WriteLine(userTables.Rows[i]["Table_Name"].ToString());
}
}
catch (Exception ee)
{
GlobalDefine.SP.LastErrorMsg = ee.Message;
#if DEBUG
Debug.WriteLine(ee.Message);
Debug.WriteLine(ee.StackTrace);
throw ee;
#else
GlobalDefine.SP.LastErrorMsg = ee.Message;
#endif
}
finally
{
if (reader != null)
{
reader.Dispose();
}
}
}
return(jieguo);
}
public void ADOCommandBuilderTest1()
{
string connString = @"Connection Type=File ; Initial Catalog=D:\T2; User=sa; Password=;";
string sql = "CREATE TABLE TEST(ID INT,ID2 INT, NAME VARCHAR(100),ID3 INT,ID4 INT DEFAULT 5, PRIMARY KEY(ID), UNIQUE (ID2), UNIQUE (ID3,ID4));";
using (EfzConnection conn = new EfzConnection(connString))
{
DbCommand command = conn.CreateCommand();
command.CommandText = sql;
conn.Open();
int count = command.ExecuteNonQuery();
//command.CommandText = "INSERT INTO TEST(ID , ID2, NAME ) VALUES(1, 100,'irantha'); INSERT INTO TEST(ID ,ID2, NAME ) VALUES(2, 500,'subash');";
//count = command.ExecuteNonQuery();
//Assert.AreEqual(count, 1);
DataTable tb = conn.GetSchema("Tables", new string[] { null, null, null, "TABLE" });
Assert.IsNotNull(tb);
string tableName = tb.Rows[0]["Table_Name"].ToString();
Assert.AreEqual(tableName, "TEST");
Assert.AreEqual(1, tb.Rows.Count);
tb = conn.GetSchema("Columns", new string[] { null, "PUBLIC", "TEST", "ID" });
Assert.AreEqual(1, tb.Rows.Count);
tb = conn.GetSchema("Columns", new string[] { null, "PUBLIC", "TEST", "NAME" });
Assert.AreEqual(1, tb.Rows.Count);
tb = conn.GetSchema("PRIMARYKEYS", new string[] { null, "PUBLIC", "TEST" });
Assert.AreEqual(1, tb.Rows.Count);
tb = conn.GetSchema("INDEXES", new string[] { null, "PUBLIC", "TEST" });
Assert.AreEqual(3, tb.Rows.Count);
tb = conn.GetSchema("INDEXES", new string[] { null, "PUBLIC", "TEST", null, "true" });
Assert.AreEqual(3, tb.Rows.Count);
tb = conn.GetSchema("SCHEMAS");
Assert.AreEqual(3, tb.Rows.Count);
tb = conn.GetSchema("TYPES");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("DataTypes");
Assert.IsTrue(tb.Rows.Count > 0);
//PrintDT(tb);
command = conn.CreateCommand();
command.CommandText = "SELECT * FROM TEST";
DbDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);
DataTable columnSchemaTbl = reader.GetSchemaTable();
Assert.IsTrue((bool)columnSchemaTbl.Rows[0]["IsKey"]);
Assert.IsTrue((bool)columnSchemaTbl.Rows[0]["IsUnique"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[1]["IsKey"]);
Assert.IsTrue((bool)columnSchemaTbl.Rows[1]["IsUnique"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[2]["IsUnique"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[3]["IsUnique"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[2]["IsKey"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[3]["IsKey"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[4]["IsUnique"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[4]["IsKey"]);
Assert.AreEqual("5", columnSchemaTbl.Rows[4][SchemaTableOptionalColumn.DefaultValue]);
command = conn.CreateCommand();
command.CommandText = "SELECT * FROM TEST";
reader = command.ExecuteReader();
columnSchemaTbl = reader.GetSchemaTable();
Assert.IsFalse((bool)columnSchemaTbl.Rows[0]["IsKey"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[1]["IsKey"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[1]["IsUnique"]);
tb = new DataTable("Test");
tb.Load(reader);
tb = conn.GetSchema("METADATACOLLECTIONS");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("CharacterSets");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("CheckConstraints");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("Collations");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("Domains");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("ColumnPrivilages");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("PROCEDURES");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("PROCEDUREPARAMETERS");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("TABLEPRIVILEGES");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("TableConstraints");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("TRIGGERS", new string[] { });
Assert.IsTrue(tb.Rows.Count == 0);
tb = conn.GetSchema("VIEWS");
int oldViewCount = tb.Rows.Count;
command = conn.CreateCommand();
command.CommandText = "CREATE VIEW v1 AS SELECT ID,NAME FROM TEST";
command.ExecuteNonQuery();
tb = conn.GetSchema("VIEWS");
Assert.IsTrue(tb.Rows.Count == oldViewCount + 1);
tb = conn.GetSchema("ViewColumns");
Assert.IsTrue(tb.Rows.Count > 0);
command = conn.CreateCommand();
command.CommandText = "CREATE TABLE TEST2(IDD INT,IDD2 INT, NAME VARCHAR(100), FOREIGN KEY(IDD2)" +
" REFERENCES TEST(ID));";
command.ExecuteNonQuery();
tb = conn.GetSchema("EXPORTEDKEYS");
Assert.IsTrue(tb.Rows.Count == 1);
tb = conn.GetSchema("IMPORTEDKEYS");
Assert.IsTrue(tb.Rows.Count == 1);
tb = conn.GetSchema("FOREIGNKEYS");
Assert.IsTrue(tb.Rows.Count == 1);
tb = conn.GetSchema("ForeignKeyColumns");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("RESERVEDWORDS");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("RESTRICTIONS");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("FUNCTIONS");
tb = conn.GetSchema("FunctionParameters");
// Assert.IsTrue(tb.Rows.Count> 0);
// PrintDT(tb);
}
}