в 15:31, 26 мая 2010

2010-05-26T15:31:19Z

Admin: 1 версия

2010-05-26T11:42:43Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 15:31, 26 мая 2010
(нет различий)

Csharp/C Sharp/Database ADO.net/Table Schema - История изменений

в 15:31, 26 мая 2010

Admin: 1 версия