Get a table's primary keys

DataSet data = new DataSet();
SqlConnection connection = new SqlConnection(Properties.Settings.Default.connectionString);
SqlCommand command = new SqlCommand();
string commandtext = "SELECT TOP 1 * FROM " + tabelName;
command.CommandText = commandtext;
command.Connection = connection;
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.FillSchema(data, SchemaType.Source);

DataColumn[] pks = data.Tables[0].PrimaryKey;

Get table names of all tables containing a given column

DataSet dsTableNames = new DataSet();
SqlConnection connection = new SqlConnection(Properties.Settings.Default.connectionString);
SqlCommand command = new SqlCommand();
string commandtext = "SELECT sys.sysobjects.name " +
                     "FROM sys.sysobjects INNER JOIN " +
                     "sys.syscolumns ON sys.sysobjects.id = sys.syscolumns.id " +
                     "WHERE (sys.syscolumns.name = @Column)";
command.CommandText = commandtext;
command.Parameters.Add(new SqlParameter("@Column", columnName));
command.Connection = connection;
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(dsTableNames, "tableNames");

Get column names from a SQL table

DataTable dtColumnNames = new DataTable();
SqlCommand command = new SqlCommand();
SqlConnection connection = GetConnection();
string commandText = "SELECT name FROM sys.syscolumns WHERE (id = OBJECT_ID(@Table))";
command.Connection = connection;
command.CommandText = commandText;
command.Parameters.Add(new SqlParameter("@Table", tableName));
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(dtColumnNames);

Reading a file from a SQL BLOB column

Counterpart to the previous post.

Byte[] output = new Byte[255];
long blob = 0;
FileStream stream;
BinaryWriter writer;
SqlConnection connection = DatabaseManager.GetConnection();
string commandText = "SELECT Name, Extension, Blob FROM tablename WHERE FileId=@Id";
SqlCommand command = new SqlCommand(commandText, connection);
command.Connection = connection;
command.CommandText = commandText;
command.Parameters.Add(new SqlParameter("@Id", fileId));
SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
string fileName = "";
string fileExtension = "";
string filePath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);               
while (reader.Read())
{
   fileName = reader.GetString(0);
   fileExtension = reader.GetString(1);
   string path = filePath + @"\" + fileName + fileExtension;
   stream = new FileStream(pad, FileMode.OpenOrCreate,     FileAccess.Write);
   writer = new BinaryWriter(stream);
   long startIndex = 0;
   blob = reader.GetBytes(2, startIndex, output, 0, 255);
   while (blob == 255)
   {
       writer.Write(output);
       writer.Flush();
       startIndex += 255;
       blob = reader.GetBytes(2, startIndex, output, 0, 255);
   }
   writer.Write(output, 0, (int)blob);
   writer.Flush();
   writer.Close();
   stream.Close();     
}

Inserting a file into a SQL BLOB column

One way to insert files into an SQL database. The file can be anything, an image, word document, etc. Just change the extension to your liking.

SqlCommand command = new SqlCommand();
SqlConnection connection = DatabaseManager.GetConnection();
string commandText = "INSERT INTO tablename VALUES(@Name, @Extension, @Blob)";
command.Connection = connection;
command.CommandText = commandText;
command.Parameters.Add(new SqlParameter("@Name ", fileName));
command.Parameters.Add(new SqlParameter("@Extension ", fileExtension));
FileStream stream = new FileStream(filepath, FileMode.Open, FileAccess.Read);
Byte[] blob = new Byte[stream.Length];
stream.Read(blob, 0, blob.Length);
stream.Close();
stream.Dispose();
command.Parameters.Add(new SqlParameter("@Blob", blob));
command.ExecuteNonQuery();