SQL CLR examples using OleDbCommand and connection.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using Microsoft.SqlServer.Server;
namespace ErikEJ.SqlCe
{
public static class ClrAccess
{
[SqlProcedure()]
public static void GetTable(string connectionString, string tableName)
{
var metaCount = 0;
var fieldNames = new List<string>();
//--use: "Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;OLE DB Services=-4;" for SQL Compact 3.1
//--use: "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;OLE DB Services=-4;" for SQL Compact 3.5 SP2
//--use: "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;OLE DB Services=-4;" for SQL Compact 4.0
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
// determine the number of SqlMetadata parameters needed
using (var cmd = new OleDbCommand())
{
cmd.CommandText = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @p1 ORDER BY ORDINAL_POSITION";
cmd.Parameters.Add(new OleDbParameter("@p1", OleDbType.VarWChar, 128));
cmd.Parameters[0].Value = tableName;
cmd.Connection = conn;
using (var rdr = cmd.ExecuteReader())
{
while (rdr != null && rdr.Read())
{
//if (SqlContext.Pipe != null) SqlContext.Pipe.Send(rdr[1].ToString());
if (rdr[1].ToString() == "ntext" || rdr[1].ToString() == "image") continue;
metaCount++;
fieldNames.Add("[" + rdr[0] + "]");
}
}
}
if (metaCount == 0)
{
if (SqlContext.Pipe != null) SqlContext.Pipe.Send("No data found, or table does not exist");
return;
}
//Get the meta data for the fields
var metadata = GetMetaData(metaCount, tableName, conn);
var record = new SqlDataRecord(metadata);
var fields = new System.Text.StringBuilder();
foreach (var field in fieldNames)
{
fields.Append(field);
fields.Append(", ");
}
fields.Remove(fields.Length - 2, 2);
using (var cmd = new OleDbCommand("SELECT " + fields + " FROM [" + tableName + "]", conn))
{
using (var rdr = cmd.ExecuteReader())
{
if (SqlContext.Pipe != null)
{
//SqlContext.Pipe.Send(cmd.CommandText);
SqlContext.Pipe.SendResultsStart(record);
while (rdr != null && rdr.Read())
{
for (var i = 0; i < rdr.FieldCount; i++)
{
if (rdr.IsDBNull(i))
{
record.SetDBNull(i);
}
else
{
if (metadata[i].SqlDbType == SqlDbType.Bit)
{
record.SetBoolean(i, rdr.GetBoolean(i));
}
if (metadata[i].SqlDbType == SqlDbType.TinyInt)
{
record.SetByte(i, rdr.GetByte(i));
}
if (metadata[i].SqlDbType == SqlDbType.SmallInt)
{
record.SetInt16(i, rdr.GetInt16(i));
}
if (metadata[i].SqlDbType == SqlDbType.Int)
{
record.SetInt32(i, rdr.GetInt32(i));
}
if (metadata[i].SqlDbType == SqlDbType.BigInt)
{
record.SetInt64(i, rdr.GetInt64(i));
}
if (metadata[i].SqlDbType == SqlDbType.NVarChar || metadata[i].SqlDbType == SqlDbType.NChar)
{
record.SetString(i, rdr.GetString(i));
}
if (metadata[i].SqlDbType == SqlDbType.UniqueIdentifier)
{
record.SetGuid(i, rdr.GetGuid(i));
}
if (metadata[i].SqlDbType == SqlDbType.Timestamp || metadata[i].SqlDbType == SqlDbType.Binary || metadata[i].SqlDbType == SqlDbType.VarBinary)
{
var tsbuffer = (byte[])rdr[i];
record.SetBytes(i, 0, tsbuffer, 0, tsbuffer.Length);
}
if (metadata[i].SqlDbType == SqlDbType.DateTime)
{
record.SetDateTime(i, rdr.GetDateTime(i));
}
if (metadata[i].SqlDbType == SqlDbType.Money || metadata[i].SqlDbType == SqlDbType.Decimal)
{
record.SetDecimal(i, rdr.GetDecimal(i));
}
if (metadata[i].SqlDbType == SqlDbType.Float)
{
record.SetDouble(i, rdr.GetDouble(i));
}
if (metadata[i].SqlDbType == SqlDbType.Real)
{
record.SetSqlSingle(i, Convert.ToSingle(rdr.GetValue(i)));
}
}
}
//Send the completed record..
SqlContext.Pipe.SendResultsRow(record);
}
if (rdr != null) rdr.Close();
SqlContext.Pipe.SendResultsEnd();
}
}
}
conn.Close();
}
}
/// <summary>
/// Dynamically create the metadata for each field, needed by the SqlRecord
/// </summary>
/// <param name="metaCount">Number of fields</param>
/// <param name="tableName">Name of table to inspect</param>
/// <param name="conn">An active, open connection</param>
/// <returns>Array of SqlMetaData</returns>
private static SqlMetaData[] GetMetaData(int metaCount, string tableName, OleDbConnection conn)
{
var metadata = new SqlMetaData[metaCount];
var y = 0;
using (var cmd = new OleDbCommand())
{
cmd.CommandText = "SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @p1 ORDER BY ORDINAL_POSITION";
cmd.Parameters.Add(new OleDbParameter("@p1", OleDbType.VarWChar, 128));
cmd.Parameters[0].Value = tableName;
cmd.Connection = conn;
using (var rdr = cmd.ExecuteReader())
{
while (rdr != null && rdr.Read())
{
if (rdr[1].ToString() == "ntext" || rdr[1].ToString() == "image") continue;
switch (rdr[1].ToString())
{
//Looking at all the types from "SELECT * FROM INFORMATION_SCHEMA.PROVIDER_TYPES"
case "smallint":
metadata[y] = new SqlMetaData(rdr[0].ToString(), SqlDbType.SmallInt);
y++;
break;
case "int":
metadata[y] = new SqlMetaData(rdr[0].ToString(), SqlDbType.Int);
y++;
break;
case "real":
metadata[y] = new SqlMetaData(rdr[0].ToString(), SqlDbType.Real);
y++;
break;
case "float":
metadata[y] = new SqlMetaData(rdr[0].ToString(), SqlDbType.Float);
y++;
break;
case "money":
metadata[y] = new SqlMetaData(rdr[0].ToString(), SqlDbType.Money);
y++;
break;
case "bit":
metadata[y] = new SqlMetaData(rdr[0].ToString(), SqlDbType.Bit);
y++;
break;
case "tinyint":
metadata[y] = new SqlMetaData(rdr[0].ToString(), SqlDbType.TinyInt);
y++;
break;
case "bigint":
metadata[y] = new SqlMetaData(rdr[0].ToString(), SqlDbType.BigInt);
y++;
break;
case "uniqueidentifier":
metadata[y] = new SqlMetaData(rdr[0].ToString(), SqlDbType.UniqueIdentifier);
y++;
break;
case "varbinary":
metadata[y] = new SqlMetaData(rdr[0].ToString(), SqlDbType.VarBinary, Convert.ToInt64(rdr[2].ToString()));
y++;
break;
case "binary":
metadata[y] = new SqlMetaData(rdr[0].ToString(), SqlDbType.Binary, Convert.ToInt64(rdr[2].ToString()));
y++;
break;
case "nvarchar":
metadata[y] = new SqlMetaData(rdr[0].ToString(), SqlDbType.NVarChar, Convert.ToInt64(rdr[2].ToString()));
y++;
break;
case "nchar":
metadata[y] = new SqlMetaData(rdr[0].ToString(), SqlDbType.NChar, Convert.ToInt64(rdr[2].ToString()));
y++;
break;
case "numeric":
metadata[y] = new SqlMetaData(rdr[0].ToString(), SqlDbType.Decimal, Convert.ToByte(rdr[3].ToString()), Convert.ToByte(rdr[4].ToString()));
y++;
break;
case "datetime":
metadata[y] = new SqlMetaData(rdr[0].ToString(), SqlDbType.DateTime);
y++;
break;
case "rowversion":
metadata[y] = new SqlMetaData(rdr[0].ToString(), SqlDbType.Timestamp);
y++;
break;
}
}
}
}
return metadata;
}
}
}