using System;
using System.Collections.Generic;
using System.Data;
using System.Collections;
using System.Configuration;
using System.Windows.Forms;
using System.Data.SqlClient;
///
/// Summary description for dbProvider
///
///
public class dbClass
{
//string constring = ConfigurationSettings.AppSettings["constring"];
//string constring = ConfigurationSettings.AppSettings["constring"].ToString();
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["constring"]);
//SqlConnection conn = new SqlConnection("uid=sa;database=Tendercenter;Password=vss;Data Source=versatile3");
const int _timeout = 180;
public dbClass()
{
try
{
//string str = ConfigurationSettings.AppSettings["constring"];
//System.Windows.Forms.MessageBox.Show(str);
//string constring = ConfigurationManager.ConnectionStrings["con"];
//SqlConnection conn = new SqlConnection(constring);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
}
catch (Exception ex)
{
}
}
public dbClass(String strType)
{
if (strType == "TCOnlineSqlServer")
{
conn.ConnectionString = ConfigurationSettings.AppSettings["OnlineSql"];
MessageBox.Show(conn.ConnectionString);
//ConfigurationManager.AppSettings["TenderCenterConnectionString"];
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
try
{
conn.Open();
}
catch (Exception ex)
{
MessageBox.Show("Please check the " + strType + " Connection String " + ex.Message, "DB Connection", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public void dispose()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn = null;
}
}
public void dispose(String strType)
{
if (strType == "TCOnlineSqlServer")
{
conn.Close();
conn = null;
}
}
public SqlDataReader getData(SqlCommand cmd)
{
SqlDataReader myReader;
cmd.Connection = conn;
cmd.CommandTimeout = _timeout;
myReader = cmd.ExecuteReader();
return myReader;
}
public SqlDataReader getData(string sql)
{
SqlCommand cmd;
SqlDataReader myReader;
cmd = new SqlCommand(sql);
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
cmd.CommandTimeout = _timeout;
myReader = cmd.ExecuteReader();
return myReader;
}
//Input Sql Statement and get data object DataTable
public DataTable getData(object strQuery)
{
DataTable dt = new DataTable();
if (conn.State == ConnectionState.Open)
{
SqlDataAdapter da = new SqlDataAdapter(strQuery.ToString(), conn);
da.Fill(dt);
}
return dt;
}
//Input Sql Statement and get data object DataSet
public DataSet getData(string sql, string tableName)
{
DataSet myDataset = new DataSet();
if (conn.State == ConnectionState.Open)
{
SqlDataAdapter myAdapter;
myAdapter = new SqlDataAdapter(sql, conn);
myAdapter.Fill(myDataset, tableName);
}
return myDataset;
}
//Input Sql Statement and get data object DataSet
public DataSet getData(SqlCommand cmd, string tableName)
{
SqlDataAdapter myAdapter;
DataSet myDataset = new DataSet();
cmd.Connection = conn;
cmd.CommandTimeout = _timeout;
myAdapter = new SqlDataAdapter(cmd);
myAdapter.Fill(myDataset, tableName);
return myDataset;
}
//Input Stored procedure and Mode to get data object Datatable
public DataTable getData(string StoredProcedure, String[,] Parameters, string tableName)
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(StoredProcedure);
cmd.Connection = conn;
cmd.CommandTimeout = _timeout;
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i <>
{
cmd.Parameters.AddWithValue(Parameters[i, 0].ToString(), Parameters[i, 1].ToString());
}
//cmd.Parameters.AddWithValue("@Mode", Mode);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
dt.TableName = tableName;
return dt;
}
public DataTable getDataTable(string StoredProcedure, Object[,] Parameters)
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(StoredProcedure);
cmd.Connection = conn;
cmd.CommandTimeout = _timeout;
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i <>
{
cmd.Parameters.AddWithValue(Parameters[i, 0].ToString(), Parameters[i, 1]);
}
//cmd.Parameters.AddWithValue("@Mode", Mode);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
//dt.TableName = tableName;
return dt;
}
public SqlDataReader getData(string StoredProcedure, Object[,] Parameters)
{
SqlDataReader dr;
SqlCommand cmd = new SqlCommand(StoredProcedure);
cmd.Connection = conn;
cmd.CommandTimeout = _timeout;
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i <>
{
cmd.Parameters.AddWithValue(Parameters[i, 0].ToString(), Parameters[i, 1].ToString());
}
dr = cmd.ExecuteReader();
return dr;
}
public int setData(string StoredProcedure, Object[,] Parameters)
{
SqlCommand cmd = new SqlCommand(StoredProcedure);
cmd.Connection = conn;
cmd.CommandTimeout = _timeout;
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i <>
{
cmd.Parameters.AddWithValue(Parameters[i, 0].ToString(), Parameters[i, 1]);
}
return (int)cmd.ExecuteNonQuery();
}
public string setData(string StoredProcedure, Object[,] Parameters, string outParam)
{
SqlCommand cmd = new SqlCommand(StoredProcedure);
cmd.Connection = conn;
cmd.CommandTimeout = _timeout;
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i <>
{
cmd.Parameters.AddWithValue(Parameters[i, 0].ToString(), Parameters[i, 1]);
}
SqlParameter parmId;
parmId = cmd.Parameters.Add(outParam, SqlDbType.VarChar, 50);
parmId.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
return Convert.ToString(cmd.Parameters[outParam].Value);
}
//this function used to store the data into the database
public void setData(SqlCommand cmd)
{
cmd.Connection = conn;
cmd.CommandTimeout = _timeout;
cmd.ExecuteNonQuery();
}
public string setData(SqlCommand cmd, string outParam)
{
SqlParameter parmId;
parmId = cmd.Parameters.Add(outParam, SqlDbType.VarChar, 50);
parmId.Direction = ParameterDirection.Output;
cmd.Connection = conn;
cmd.CommandTimeout = _timeout;
cmd.ExecuteNonQuery();
return Convert.ToString(cmd.Parameters[outParam].Value);
}
public string getID(string strTableName, string strColumnName, string strWhereColName, string strWhereColValue)
{
try
{
dbClass objDB = new dbClass();
if (strWhereColValue != String.Empty)
{
SqlDataReader objDr;
objDr = objDB.getData("select " + strColumnName + " from " + strTableName + " where " + strWhereColName + " = '" + strWhereColValue + "'");
if (objDr.HasRows)
{
objDr.Read();
return objDr[0].ToString();
}
else
return "0";
}
else
{
return "0";
}
objDB.dispose();
}
catch (Exception ex)
{
return "0";
}
}
public SqlConnection GetSqlConnection()
{
return conn;
}
}
No comments:
Post a Comment