Csharp/C Sharp/Database ADO.net/Table Column

Материал из .Net Framework эксперт
Перейти к: навигация, поиск

Geting Table column data type

<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"});
  foreach(DataColumn col in tables.Columns) 
    Console.WriteLine("{0}\t{1}", col.ColumnName, col.DataType);
  con.Close();
}

}


      </source>


Read column values as C# types using the "Get" methods

<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; class StronglyTypedColumnValues {

 public static void Main()
 {
   SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
   SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
   mySqlCommand.rumandText = "SELECT TOP 5 ProductID, ProductName, UnitPrice, " +
     "UnitsInStock, Discontinued " +
     "FROM Products " +
     "ORDER BY ProductID";
   mySqlConnection.Open();
   SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader();
   int productIDColPos = productsSqlDataReader.GetOrdinal("ProductID");
   int productNameColPos = productsSqlDataReader.GetOrdinal("ProductName");
   int unitPriceColPos = productsSqlDataReader.GetOrdinal("UnitPrice");
   int unitsInStockColPos = productsSqlDataReader.GetOrdinal("UnitsInStock");
   int discontinuedColPos = productsSqlDataReader.GetOrdinal("Discontinued");
   Console.WriteLine("ProductID .NET type = " + productsSqlDataReader.GetFieldType(productIDColPos));
   Console.WriteLine("ProductName .NET type = " + productsSqlDataReader.GetFieldType(productNameColPos));
   Console.WriteLine("UnitPrice .NET type = " + productsSqlDataReader.GetFieldType(unitPriceColPos));
   Console.WriteLine("UnitsInStock .NET type = " + productsSqlDataReader.GetFieldType(unitsInStockColPos));
   Console.WriteLine("Discontinued .NET type = " + productsSqlDataReader.GetFieldType(discontinuedColPos));
   Console.WriteLine("ProductID database type = " + productsSqlDataReader.GetDataTypeName(productIDColPos));
   Console.WriteLine("ProductName database type = " + productsSqlDataReader.GetDataTypeName(productNameColPos));
   Console.WriteLine("UnitPrice database type = " + productsSqlDataReader.GetDataTypeName(unitPriceColPos));
   Console.WriteLine("UnitsInStock database type = " + productsSqlDataReader.GetDataTypeName(unitsInStockColPos));
   Console.WriteLine("Discontinued database type = " + productsSqlDataReader.GetDataTypeName(discontinuedColPos));
   while (productsSqlDataReader.Read())
   {
     int productID = productsSqlDataReader.GetInt32(productIDColPos);
     Console.WriteLine("productID = " + productID);
     string productName = productsSqlDataReader.GetString(productNameColPos);
     Console.WriteLine("productName = " + productName);
     decimal unitPrice = productsSqlDataReader.GetDecimal(unitPriceColPos);
     Console.WriteLine("unitPrice = " + unitPrice);
     short unitsInStock = productsSqlDataReader.GetInt16(unitsInStockColPos);
     Console.WriteLine("unitsInStock = " + unitsInStock);
     bool discontinued = productsSqlDataReader.GetBoolean(discontinuedColPos);
     Console.WriteLine("discontinued = " + discontinued);
   }
   productsSqlDataReader.Close();
   mySqlConnection.Close();
 }

}


      </source>


Read column values as Sql* types using the GetSql* methods

   <source lang="csharp">

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; class StronglyTypedColumnValuesSql {

 public static void Main()
 {
   SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
   SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
   mySqlCommand.rumandText =
      "SELECT TOP 5 ID, Name, UnitPrice, " +
      "UnitsInStock, Discontinued " +
      "FROM Products " +
      "ORDER BY ProductID";
   mySqlConnection.Open();
   SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader();
   int productIDColPos = productsSqlDataReader.GetOrdinal("ID");
   int productNameColPos = productsSqlDataReader.GetOrdinal("Name");
   int unitPriceColPos = productsSqlDataReader.GetOrdinal("UnitPrice");
   int unitsInStockColPos = productsSqlDataReader.GetOrdinal("UnitsInStock");
   int discontinuedColPos = productsSqlDataReader.GetOrdinal("Discontinued");
   while (productsSqlDataReader.Read())
   {
     SqlInt32 productID = productsSqlDataReader.GetSqlInt32(productIDColPos);
     Console.WriteLine("productID = " + productID);
     SqlString productName = productsSqlDataReader.GetSqlString(productNameColPos);
     Console.WriteLine("productName = " + productName);
     SqlMoney unitPrice = productsSqlDataReader.GetSqlMoney(unitPriceColPos);
     Console.WriteLine("unitPrice = " + unitPrice);
     SqlInt16 unitsInStock = productsSqlDataReader.GetSqlInt16(unitsInStockColPos);
     Console.WriteLine("unitsInStock = " + unitsInStock);
     SqlBoolean discontinued = productsSqlDataReader.GetSqlBoolean(discontinuedColPos);
     Console.WriteLine("discontinued = " + discontinued);
   }
   productsSqlDataReader.Close();
   mySqlConnection.Close();
 }

}

      </source>
   
  


Refernece column name in SqlDataReader

<source lang="csharp"> using System; using System.Data.SqlClient; class FirstExample {

 public static void Main()
 {
   try
   {
     SqlConnection mySqlConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
     SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
     mySqlCommand.rumandText =  "SELECT id, firstname, lastname from employee";
     mySqlConnection.Open();
     SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
     mySqlDataReader.Read();
     Console.WriteLine("mySqlDataReader[\" ID\"] = "+ mySqlDataReader["ID"]);
     Console.WriteLine("mySqlDataReader[\" First Name\"] = "+ mySqlDataReader["FirstName"]);
     Console.WriteLine("mySqlDataReader[\" Last Name\"] = "+ mySqlDataReader["LastName"]);
     mySqlDataReader.Close();
     mySqlConnection.Close();
   }
   catch (SqlException e)
   {
     Console.WriteLine("A SqlException was thrown");
     Console.WriteLine("Number = "+ e.Number);
     Console.WriteLine("Message = "+ e.Message);
     Console.WriteLine("StackTrace:\n" + e.StackTrace);
   }
 }

}

      </source>