C# - using a dll with code behind 1

This is an example of using a dll file to call data from to populate a c# code behind file.


1. An example of a dll file to call data from a sql database


using System;


using System.Collections.Generic;


using System.Collections;


using System.ComponentModel;


using System.Configuration;


using System.Data.Common;


using System.Data.Odbc;


using System.Data.OleDb;


using System.Data.Sql;


using System.Data.SqlClient;


using System.Data.SqlTypes;


using System.Data;


using System.Diagnostics;


using System.Drawing;


using System.IO;


using System.Linq;


using System.Net.Mail;


using System.Net;


using System.Runtime.Serialization.Formatters.Binary;


using System.Security.Principal;


using System.Text.RegularExpressions;


using System.Text;


using Microsoft.VisualBasic;


using System.Web;


using System.Xml;


namespace TestLibrary1


{


public class DataBaseDll3
{


public DataSet GetData1(string cmdText)


{


DataSet dt = new DataSet("Test");

SqlCommand cmd = new SqlCommand();

SqlDataAdapter da = new SqlDataAdapter();

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["LIVE"].ConnectionString))


{

da.SelectCommand = con.CreateCommand();

da.SelectCommand.CommandType = CommandType.Text;

da.SelectCommand.CommandText = cmdText;

da.SelectCommand.Connection = con;



try

{
con.Open();

da.Fill(dt);

con.Close();

}

catch (Exception ec)
{ }
finally



{
con.Close();
}


}


return dt;




}

}


DON'T FORGET TO ADD THE CONNECTION STRING IN THE WEB.CONFIG FILE IN THE DLL


Then to call the result in the Code Behind..


ADD



using ;


Add the .dll file through the Reference Folder in Visual Studio


Use Browse and find the file in the bin/debug folder.


The add the follow code to call the DataSet .dll result...





DataBaseDll3 db2 = new DataBaseDll3();

Global.ds = db2.GetData1("Select GetDate();");

string test2 = Global.ds.Tables[0].Rows[0][0].ToString();

XML - Read XML data type from SQL database in C#

The code below pulls the Column with XML Data Type from the table in the SQL DATABASE
and keeps the data in XML format in c#. 


string connStr = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;

string query = " FOR XML AUTO, ROOT";

// wrap your SqlConnection and SqlCommand in using blocks...

using (SqlConnection _con = new SqlConnection(connStr))

using (SqlCommand _cmd = new SqlCommand(query, _con))



{

// setup parameter for _cmd

// _cmd.Parameters.Add("@ID", SqlDbType.Int).Value = 1;



_con.Open();

string contentsOfYourXml = (string)_cmd.ExecuteScalar();



_con.Close();

Label1.Text = contentsOfYourXml;

}