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.CommandText =
"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();
}
}
Database ADO.net
how to read column values as C# types using the Get* methods
using System;
using System.Data;
using System.Data.SqlClient;
class StronglyTypedColumnValues {
public static void Main() {
SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText =
"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();
}
}
use the GetOrdinal() from DataReader to get the numeric positions of a column
using System;
using System.Data;
using System.Data.SqlClient;
class UsingColumnOrdinals {
public static void Main() {
SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText =
"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");
while (productsSqlDataReader.Read()) {
Console.WriteLine("ProductID = " + productsSqlDataReader[productIDColPos]);
Console.WriteLine("ProductName = " + productsSqlDataReader[productNameColPos]);
Console.WriteLine("UnitPrice = " + productsSqlDataReader[unitPriceColPos]);
Console.WriteLine("UnitsInStock = " + productsSqlDataReader[unitsInStockColPos]);
Console.WriteLine("Discontinued = " + productsSqlDataReader[discontinuedColPos]);
}
productsSqlDataReader.Close();
mySqlConnection.Close();
}
}
Get column index from SqlDataReader
using System;
using System.Data.SqlClient;
class ConnectToSqlConnection {
static void Main(string[] args) {
String sConn = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
String sSQL = "select id, firstname, lastname from Employee";
SqlConnection oConn = new SqlConnection(sConn);
oConn.Open();
SqlCommand oCmd = new SqlCommand(sSQL, oConn);
SqlDataReader oReader = oCmd.ExecuteReader();
int idxID = oReader.GetOrdinal("id");
int idxFirstName = oReader.GetOrdinal("firstname");
int idxLastName = oReader.GetOrdinal("lastname");
while(oReader.Read()) {
Console.WriteLine("{0} {1} {2}",
oReader.GetValue(idxID),
oReader.GetValue(idxFirstName),
oReader.GetValue(idxLastName));
}
}
}
Execute multiple SELECT statements using a SqlCommand object and read the results using a SqlDataReader object
using System;
using System.Data;
using System.Data.SqlClient;
class ExecuteSelect
{
public static void Main()
{
SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText =
"SELECT TOP 5 ProductID, ProductName " +
"FROM Products " +
"ORDER BY ProductID;" +
"SELECT TOP 3 CustomerID, CompanyName " +
"FROM Customers " +
"ORDER BY CustomerID;" +
"SELECT TOP 6 OrderID, CustomerID " +
"FROM Orders " +
"ORDER BY OrderID;";
mySqlConnection.Open();
SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
do
{
while (mySqlDataReader.Read())
{
Console.WriteLine("mySqlDataReader[0] = " + mySqlDataReader[0]);
Console.WriteLine("mySqlDataReader[1] = " + mySqlDataReader[1]);
}
Console.WriteLine("");
} while (mySqlDataReader.NextResult());
mySqlDataReader.Close();
mySqlConnection.Close();
}
}
Update database using the SqlDataAdapter
using System;
using System.Data;
using System.Data.SqlClient;
class Class1{
static void Main(string[] args){
SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT ID, FirstName FROM Employee", thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "Employee");
Console.WriteLine("name before change: {0}", thisDataSet.Tables["Employee"].Rows[9]["FirstName"]);
thisDataSet.Tables["Employee"].Rows[1]["FirstName"] = "Inc";
thisAdapter.Update(thisDataSet, "Employee");
Console.WriteLine("name after change: {0}", thisDataSet.Tables["Employee"].Rows[9]["FirstName"]);
}
}
Delete data from database table using SqlDataAdapter
using System;
using System.Data;
using System.Data.SqlClient;
class PropagateDeletes {
static void Main() {
string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
string qry = @"select * from employee ";
string del = @"delete from employee where id = @id";
SqlConnection conn = new SqlConnection(connString);
try {
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds, "employee");
DataTable dt = ds.Tables["employee"];
SqlCommand cmd = new SqlCommand(del, conn);
cmd.Parameters.Add("@id",SqlDbType.Int, 4, "id");
string filt = @"firstname = 'o' and lastname = 'B'";
foreach (DataRow row in dt.Select(filt)) {
row.Delete();
}
da.DeleteCommand = cmd;
da.Update(ds, "employee");
foreach (DataRow row in dt.Rows) {
Console.WriteLine(
"{0} {1}",
row["firstname"].ToString().PadRight(15),
row["lastname"].ToString().PadLeft(25));
}
} catch(Exception e) {
Console.WriteLine("Error: " + e);
} finally {
conn.Close();
}
}
}