Csharp/C Sharp/Database ADO.net/Table Schema — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 18:31, 26 мая 2010
Содержание
Get all table names
<source lang="csharp"> using System; using System.Data; using System.Data.OleDb; public class DatabaseInfo {
public static void Main () { String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb"; OleDbConnection con = new OleDbConnection(connect); con.Open(); Console.WriteLine("Made the connection to the database"); Console.WriteLine("Information for each table contains:"); DataTable tables = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"}); Console.WriteLine("The tables are:"); foreach(DataRow row in tables.Rows) Console.Write(" {0}", row[2]);
con.Close(); }
}
</source>
Get Column data type and name from DataColumn
<source lang="csharp"> using System; using System.Data; using System.Data.OleDb; public class DatabaseInfo {
public static void Main () { String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb"; OleDbConnection con = new OleDbConnection(connect); con.Open(); Console.WriteLine("Made the connection to the database"); String cmd = "SELECT * FROM Employee"; OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = new OleDbCommand(cmd, con); DataSet ds = new DataSet(); adapter.Fill(ds, "Employee"); DataTable item = ds.Tables[0]; Console.WriteLine("Table name: {0}", item.TableName); Console.WriteLine("Its columns are:"); foreach (DataColumn col in item.Columns) Console.WriteLine("{0}\t{1}", col.ColumnName, col.DataType); con.Close(); }
}
</source>
Get specified column data type and column name from OleDbSchemaTable
<source lang="csharp"> using System; using System.Data; using System.Data.OleDb; public class DatabaseInfo {
public static void Main () { String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb"; OleDbConnection con = new OleDbConnection(connect); con.Open(); Console.WriteLine("Made the connection to the database"); Console.WriteLine("Information for each table contains:"); DataTable tables = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"}); DataTable cols = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[]{null,null,"Employee",null}); Console.WriteLine("The columns in the Customer table are:"); foreach(DataRow row in cols.Rows) Console.WriteLine(" {0}\t{1}", row[3],(OleDbType)row[11]); con.Close(); }
}
</source>
Get table Schema
<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient;
class SchemaTable { static void Main(string[] args) { string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; string sql = @"select * from employee"; SqlConnection conn = new SqlConnection(connString); try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader reader = cmd.ExecuteReader(); DataTable schema = reader.GetSchemaTable(); foreach (DataRow row in schema.Rows) { foreach (DataColumn col in schema.Columns){ Console.WriteLine(col.ColumnName + " = " + row[col]); Console.WriteLine("Null value allowed: " + col.AllowDBNull); } } reader.Close(); } catch(Exception e) { Console.WriteLine("Error Occurred: " + e); } finally { conn.Close(); } } }
</source>
How to read a table schema
<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; class SchemaOnlyCommandBehavior {
public static void Main() { SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.rumandText ="SELECT ID, FirstName, LastName FROM Employee WHERE ID = 8"; mySqlConnection.Open(); SqlDataReader productsSqlDataReader =mySqlCommand.ExecuteReader(CommandBehavior.SchemaOnly); DataTable myDataTable = productsSqlDataReader.GetSchemaTable(); foreach (DataRow myDataRow in myDataTable.Rows) { Console.WriteLine("\nNew column details follow:"); foreach (DataColumn myDataColumn in myDataTable.Columns) { Console.WriteLine(myDataColumn + "= " + myDataRow[myDataColumn]); if (myDataColumn.ToString() == "ProviderType") { Console.WriteLine(myDataColumn + "= " + ((System.Data.SqlDbType) myDataRow[myDataColumn])); } } } productsSqlDataReader.Close(); mySqlConnection.Close(); }
}
</source>
Read schema information using the FillSchema() method of a DataAdapter object
<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; class FillSchema {
public static void Main() { SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.rumandText = "SELECT ProductID, ProductName " + "FROM Products;" + "SELECT OrderID " + "FROM Orders;" + "SELECT OrderID, ProductID, UnitPrice " + "FROM [Order Details];"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); mySqlDataAdapter.FillSchema(myDataSet, SchemaType.Mapped); mySqlConnection.Close(); myDataSet.Tables["Table"].TableName = "Products"; myDataSet.Tables["Table1"].TableName = "Orders"; myDataSet.Tables["Table2"].TableName = "Order Details"; foreach (DataTable myDataTable in myDataSet.Tables) { Console.WriteLine("\n\nReading from the " + myDataTable + "DataTable:\n"); foreach (DataColumn myPrimaryKey in myDataTable.PrimaryKey) { Console.WriteLine("myPrimaryKey = " + myPrimaryKey); } foreach (Constraint myConstraint in myDataTable.Constraints) { Console.WriteLine("myConstraint.IsPrimaryKey = " + ((UniqueConstraint) myConstraint).IsPrimaryKey); foreach (DataColumn myDataColumn in ((UniqueConstraint) myConstraint).Columns) { Console.WriteLine("myDataColumn.ColumnName = " + myDataColumn.ColumnName); } } foreach (DataColumn myDataColumn in myDataTable.Columns) { Console.WriteLine("\nmyDataColumn.ColumnName = " + myDataColumn.ColumnName); Console.WriteLine("myDataColumn.DataType = " + myDataColumn.DataType); Console.WriteLine("myDataColumn.AllowDBNull = " + myDataColumn.AllowDBNull); Console.WriteLine("myDataColumn.AutoIncrement = " + myDataColumn.AutoIncrement); Console.WriteLine("myDataColumn.AutoIncrementSeed = " + myDataColumn.AutoIncrementSeed); Console.WriteLine("myDataColumn.AutoIncrementStep = " + myDataColumn.AutoIncrementStep); Console.WriteLine("myDataColumn.MaxLength = " + myDataColumn.MaxLength); Console.WriteLine("myDataColumn.ReadOnly = " + myDataColumn.ReadOnly); Console.WriteLine("myDataColumn.Unique = " + myDataColumn.Unique); } } }
}
</source>