Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Text;
Using System.Data;
Using System.Data.SqlClient;
Namespace Datacontroller
{
Static Class SqlHelper
{
const int sqltimeout = 120;
Const string Connection = "server=127.0.0.1; USER Id=sa; Pwd=password;database=database; CONNECT timeout=120; ";
<summary>
BULK INSERT Data
</summary>
<param name= "DT" > Datatable</param> to write
<param name= "tablename" > table name to insert database </param>//<param name= "ColumnNames" > column name to insert database </param >
public static void Sqlbulkcopyinsert (DataTable dt, string tablename,list<string> columnnames)
{
using (SqlConnection conn = new SqlConnection (Connection))
{
Conn. Open ();
using (SqlBulkCopy bulkcopy = new SqlBulkCopy (conn))
{
Bulkcopy.destinationtablename = tablename;
foreach (String str in columnnames)
{
BULKCOPY.COLUMNMAPPINGS.ADD (str, str);
}
Bulkcopy.writetoserver (DT);
}
}
}
public static DataTable Selectinfo (String sql)
{
DataSet ds = new DataSet ();
using (SqlConnection conn = new SqlConnection (Connection))
{
Conn. Open ();
using (SqlCommand cmd = new SqlCommand (SQL, conn))
{
Cmd.commandtimeout = Sqltimeout;
Using (SqlDataAdapter SDA = new SqlDataAdapter (cmd))
{
Sda. Fill (DS);
}
}
}
Return DS. Tables[0];
}
public static int adddelupdate (String sql)
{
using (SqlConnection conn = new SqlConnection (Connection))
{
Conn. Open ();
using (SqlCommand cmd = new SqlCommand (SQL, conn))
{
Cmd.commandtimeout = Sqltimeout;
return CMD. ExecuteNonQuery ();
}
}
return 0;
}
public static Object ExecuteScalar (String sql)
{
using (SqlConnection conn = new SqlConnection (Connection))
{
Conn. Open ();
using (SqlCommand cmd = new SqlCommand (SQL, conn))
{
Cmd.commandtimeout = Sqltimeout;
return CMD. ExecuteScalar ();
}
}
}//<summary>
Get all column names by table name///</summary>//<param name= "tablename" > table name to insert database </param> public Static list<string> Gettablecolumnnames (String tablename)
{
String sql = "SELECT name from syscolumns where id= (select Max (id) from sysobjects where xtype= ' u ' and name= '" + tablename + "')";
DataTable dt = selectinfo (SQL);
list<string> columnnames = new list<string> ();
for (int i = 0; i < dt. Rows.Count; i++)
{
Columnnames.add (dt. Rows[i][0]. ToString ());
}
return columnnames;
}
}
}
SqlHelper (with SqlBulkCopy BULK INSERT)
