Oledb and SQL Clients

While dealing with database stuff in .Net, we might worked with Oledb and SQL Clients. Here are, some of the methods which help us to get the database tables, views, primary keys...etc

OLEDB Client/ SQL Client

Namespace

System.Data.OleDb/ System.Data.SqlClient

ConnectionString

Provider=SQLOLEDB.1;Persist Security Info=False;User ID={user};Password={password};Initial Catalog={Database};Data Source={host name/IP};

Server={host name/IP}; Database={dbname}; User ID={user}; Password={sqlServerPassword};

For various connection strings, have a look at connectionstrings

Getting Table list from database

DataTable dtObj = {connection}.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object() {null, null, null, "Table"});

DataTable dtObj = {connection}.GetSchema("Tables");

Getting Views List

DataTable dtObj = {connection}.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object() {null, null, null, "View"});

DataTable dtObj = {connection}.GetSchema("Views");

Getting Stored procedures List

DataTable dtObj = {connection}.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, new Object() {null, null, null, "Views"});

DataTable dtObj = {connection}.GetSchema("Procedures");

Getting Primary keys defined in the tables

OleDbSchemaGuid.PrimaryKeys

DataTable dtObj = {connection}.GetSchema("Indexes");

Bulk insert

OledbAdapter.Update(DataTable)

SqlBulkCopy

Hence the options are vast in the Oledb and SQLClient namespaces, I have presented few things here :).

Comments

Post a Comment

Popular posts from this blog

DataTable ExtendedProperties

While playing in data tables in .net, it may require to have some temporary values such as records counts, primary key field name, …etc. In those situations, instead of declaring new variables in the program, it is ideal way to use “ExtendedProperties” option in datatable. The property is holding the collection (hashtable) values. Here is the syntax; DataTable.ExtendedProperties.Add (object key, object value); Examples; DataTable dtObj = new DataTable("employees"); dtObj.ExtendedProperties.Add("totalCount", 500); dtObj.ExtendedProperties.Add("primaryKey", "employee_id"); dtObj.ExtendedProperties.Add("createdDate", DateTime.Now); For retrieving assigned properties, int iCount = Convert.ToInt32(dtObj.ExtendedProperties["totalCount"].ToString()); So simple and effective property.
Read more

Storing Images in MySQL database

Image
We may need to store the images in the database. It is easy and pretty simple. The basic idea is; 1) The images are stored in database as bytes. In MySQL database we need to create a field/column of type " Blob ". 2) Need to use FileStream and BinaryReader objects to convert images into bytes. Here is the C# code, to do the task Table schema: C# code: MySqlConnection mcon = null; MySqlCommand cmd = null; FileStream fsObj = null; BinaryReader binRdr = null; try { //converting image to bytes fsObj = File.OpenRead(pictureBox1.ImageLocation); byte[] imgContent = new byte[fsObj.Length]; binRdr = new BinaryReader(fsObj); imgContent = binRdr.ReadBytes((int)fsObj.Length); mcon = new MySqlConnection("server=localhost;user=root;pwd=root;database=test;"); mcon.Open(); //inserting into MySQL db cmd = new MySqlCommand("insert into users (userid,username,userphoto) values (@userid, @username, @userphoto)", mcon); cmd.Parameters.Add(new MySqlPara...
Read more

XML Documentation comments

Image
When we are doing the coding, we need to use our own functions/methods to do a certain task. But while writing the function we may missed out the function details like why the function has been written . So whenever writing a function, if we use the documentation comments standard, it will help in the future whoever uses the function. Here is syntax; /// /// documentation block /// Here is the simple function defined in C# IDE; documentation comments added here; Intellisense is displaying the documentation comments.
Read more