123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290 |
- using System;
- using System.Collections.Generic;
- using System.Configuration;
- using System.Data;
- using System.Data.SqlClient;
- using System.Linq;
- using System.Text;
- namespace SIMDP.DAL.DalData
- {
- public class DataBase
- {
- private SqlConnection Con()
- {
- return new SqlConnection(ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString);
- }
- public int ExecuteSql(string sql)
- {
- SqlConnection con = this.Con();
- SqlCommand com = new SqlCommand(sql, con);
- int result;
- try
- {
- con.Open();
- com.ExecuteNonQuery();
- result = 1;
- }
- catch (SqlException e)
- {
- throw new Exception(e.Message);
- }
- finally
- {
- com.Dispose();
- con.Close();
- }
- return result;
- }
- public int ExecuteSql(string sql, SqlParameter[] p)
- {
- SqlConnection con = this.Con();
- SqlCommand com = new SqlCommand(sql, con);
- for (int i = 0; i < p.Length; i++)
- {
- com.Parameters.Add(p[i]);
- }
- int result;
- try
- {
- con.Open();
- com.ExecuteNonQuery();
- result = 1;
- }
- catch (SqlException e)
- {
- throw new Exception(e.Message);
- }
- finally
- {
- com.Parameters.Clear();
- com.Dispose();
- con.Close();
- }
- return result;
- }
- public int ExecuteSqls(string[] sql)
- {
- SqlConnection con = this.Con();
- SqlCommand com = new SqlCommand();
- int i = sql.Length;
- con.Open();
- SqlTransaction tran = con.BeginTransaction();
- int result;
- try
- {
- com.Connection = con;
- com.Transaction = tran;
- for (int j = 0; j < sql.Length; j++)
- {
- string str = sql[j];
- com.CommandText = str;
- com.ExecuteNonQuery();
- }
- tran.Commit();
- result = 1;
- }
- catch (SqlException e)
- {
- tran.Rollback();
- throw new Exception(e.Message);
- }
- finally
- {
- com.Dispose();
- con.Close();
- }
- return result;
- }
- public int ExecuteSqls(string[] sql, SqlParameter[] p)
- {
- SqlConnection con = this.Con();
- SqlCommand com = new SqlCommand();
- int i = sql.Length;
- int j = p.Length;
- for (int k = 0; k < j; k++)
- {
- com.Parameters.Add(p[k]);
- }
- con.Open();
- SqlTransaction tran = con.BeginTransaction();
- int result;
- try
- {
- com.Connection = con;
- com.Transaction = tran;
- for (int l = 0; l < sql.Length; l++)
- {
- string str = sql[l];
- com.CommandText = str;
- com.ExecuteNonQuery();
- }
- tran.Commit();
- result = 1;
- }
- catch (SqlException e)
- {
- tran.Rollback();
- throw new Exception(e.Message);
- }
- finally
- {
- com.Parameters.Clear();
- com.Dispose();
- con.Close();
- }
- return result;
- }
- public string ExecuteValue(string sql)
- {
- SqlConnection con = this.Con();
- SqlCommand com = new SqlCommand(sql, con);
- string result;
- try
- {
- con.Open();
- object ob = com.ExecuteScalar();
- if (ob != null)
- {
- result = ob.ToString();
- }
- else
- {
- result = null;
- }
- }
- catch (SqlException e)
- {
- throw new Exception(e.Message);
- }
- finally
- {
- com.Dispose();
- con.Dispose();
- }
- return result;
- }
- public string ExecuteValue(string sql, SqlParameter[] p)
- {
- SqlConnection con = this.Con();
- SqlCommand com = new SqlCommand(sql, con);
- for (int i = 0; i < p.Length; i++)
- {
- com.Parameters.Add(p[i]);
- }
- string result;
- try
- {
- con.Open();
- object ob = com.ExecuteScalar();
- if (ob != null)
- {
- result = ob.ToString();
- }
- else
- {
- result = null;
- }
- }
- catch (SqlException e)
- {
- throw new Exception(e.Message);
- }
- finally
- {
- com.Parameters.Clear();
- com.Dispose();
- con.Close();
- }
- return result;
- }
- public DataTable GetDataTable(string sql)
- {
- DataTable dt = new DataTable();
- SqlDataAdapter da = new SqlDataAdapter(sql, this.Con());
- da.Fill(dt);
- return dt;
- }
- public DataSet GetDataSet(string sql,string DataTableName)
- {
- DataSet ds = new DataSet();
- SqlDataAdapter da = new SqlDataAdapter(sql, this.Con());
- da.Fill(ds, DataTableName);
- return ds;
- }
-
- public DataTable GetDataTable(string sql, SqlParameter[] param)
- {
- DataTable dt = new DataTable();
- SqlConnection conn = this.Con();
- SqlCommand cmd = new SqlCommand(sql, conn);
- for (int i = 0; i < param.Length; i++)
- {
- cmd.Parameters.Add(param[i]);
- }
- SqlDataAdapter da = new SqlDataAdapter(cmd);
- da.Fill(dt);
- return dt;
- }
- public DataTable GetDataTableproc(string proc, SqlParameter[] param)
- {
- DataTable dt = new DataTable();
- SqlConnection conn = this.Con();
- SqlCommand cmd = new SqlCommand(proc, conn);
- cmd.CommandType = CommandType.StoredProcedure;
- for (int i = 0; i < param.Length; i++)
- {
- cmd.Parameters.Add(param[i]);
- }
- SqlDataAdapter da = new SqlDataAdapter(cmd);
- da.Fill(dt);
- return dt;
- }
- public string ExecuteValueProc(string sql, SqlParameter[] p)
- {
- SqlConnection con = this.Con();
- SqlCommand com = new SqlCommand(sql, con);
- com.CommandType = CommandType.StoredProcedure;
- for (int i = 0; i < p.Length; i++)
- {
- com.Parameters.Add(p[i]);
- }
- string result;
- try
- {
- con.Open();
- object ob = com.ExecuteScalar();
- if (ob != null)
- {
- result = ob.ToString();
- }
- else
- {
- result = null;
- }
- }
- catch (SqlException e)
- {
- throw new Exception(e.Message);
- }
- finally
- {
- com.Parameters.Clear();
- com.Dispose();
- con.Close();
- }
- return result;
- }
- public SqlDataReader getdr(string sql)
- {
- SqlConnection con = Con();
- con.Open();
- SqlCommand com = new SqlCommand(sql, con);
- SqlDataReader dr = com.ExecuteReader();
- return dr;
- }
- }
- }
|