- using System;
- using System.Collections;
- using System.Collections.Specialized;
- using System.Runtime.Remoting.Messaging;
- using System.Data;
- using System.Data.SqlClient;
- using System.Configuration;
- namespace LTP.SQLServerDAL
- {
-
-
-
- public abstract class DbManagerSQL
- {
-
- protected static string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
- public DbManagerSQL()
- {
-
-
-
- }
-
-
-
-
-
- public static int ExecuteSql(string SQLString)
- {
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- using (SqlCommand cmd = new SqlCommand(SQLString,connection))
- {
- try
- {
- connection.Open();
- int rows=cmd.ExecuteNonQuery();
- return rows;
- }
- catch(System.Data.SqlClient.SqlException E)
- {
- throw new Exception(E.Message);
- }
- }
- }
- }
-
-
-
-
-
- public static void ExecuteSqlTran(string SQLString1,string SQLString2)
- {
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- connection.Open();
- SqlCommand cmd = new SqlCommand();
- cmd.Connection=connection;
- SqlTransaction tx=connection.BeginTransaction();
- cmd.Transaction=tx;
- try
- {
- cmd.CommandText=SQLString1;
- cmd.ExecuteNonQuery();
- cmd.CommandText=SQLString2;
- cmd.ExecuteNonQuery();
- tx.Commit();
- }
- catch(System.Data.SqlClient.SqlException E)
- {
- tx.Rollback();
- throw new Exception(E.Message);
- }
- finally
- {
- cmd.Dispose();
- connection.Close();
- }
- }
- }
-
-
-
-
- public static void ExecuteSqlTran(string SQLStringList)
- {
- using (OdbcConnection conn = new OdbcConnection(connectionString))
- {
- conn.Open();
- OdbcCommand cmd = new OdbcCommand();
- cmd.Connection=conn;
- OdbcTransaction tx=conn.BeginTransaction();
- cmd.Transaction=tx;
- try
- {
- string [] split= SQLStringList.Split(new Char [] { ';'});
- foreach (string strsql in split)
- {
- if (strsql.Trim()!="")
- {
- cmd.CommandText=strsql;
- cmd.ExecuteNonQuery();
- }
- }
- tx.Commit();
- }
- catch(System.Data.Odbc.OdbcException E)
- {
- tx.Rollback();
- throw new Exception(E.Message);
- }
- }
- }
-
-
-
-
-
-
- public static int ExecuteSql(string SQLString,string content)
- {
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- SqlCommand cmd = new SqlCommand(SQLString,connection);
- System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( "@content", SqlDbType.NText);
- myParameter.Value = content ;
- cmd.Parameters.Add(myParameter);
- try
- {
- connection.Open();
- int rows=cmd.ExecuteNonQuery();
- return rows;
- }
- catch(System.Data.SqlClient.SqlException E)
- {
- throw new Exception(E.Message);
- }
- finally
- {
- cmd.Dispose();
- connection.Close();
- }
- }
- }
-
-
-
-
-
-
- public static int ExecuteSqlInsertImg(string strSQL,byte[] fs)
- {
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- SqlCommand cmd = new SqlCommand(strSQL,connection);
- System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( "@fs", SqlDbType.Image);
- myParameter.Value = fs ;
- cmd.Parameters.Add(myParameter);
- try
- {
- connection.Open();
- int rows=cmd.ExecuteNonQuery();
- return rows;
- }
- catch(System.Data.SqlClient.SqlException E)
- {
- throw new Exception(E.Message);
- }
- finally
- {
- cmd.Dispose();
- connection.Close();
- }
-
- }
- }
-
-
-
-
-
- public static int GetCount(string strSQL)
- {
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- SqlCommand cmd = new SqlCommand(strSQL,connection);
- try
- {
- connection.Open();
- SqlDataReader result = cmd.ExecuteReader();
- int i=0;
- while(result.Read())
- {
- i=result.GetInt32(0);
- }
- result.Close();
- return i;
- }
- catch(System.Data.SqlClient.SqlException e)
- {
- throw new Exception(e.Message);
- }
- finally
- {
- cmd.Dispose();
- connection.Close();
- }
- }
- }
-
-
-
-
-
- public static object GetSingle(string SQLString)
- {
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- SqlCommand cmd = new SqlCommand(SQLString,connection);
- try
- {
- connection.Open();
- object obj = cmd.ExecuteScalar();
- if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
- {
- return null;
- }
- else
- {
- return obj;
- }
- }
- catch(System.Data.SqlClient.SqlException e)
- {
- throw new Exception(e.Message);
- }
- finally
- {
- cmd.Dispose();
- connection.Close();
- }
- }
- }
-
-
-
-
-
- public static SqlDataReader ExecuteReader(string strSQL)
- {
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- SqlCommand cmd = new SqlCommand(strSQL,connection);
- SqlDataReader myReader;
- try
- {
- connection.Open();
- myReader = cmd.ExecuteReader();
- return myReader;
- }
- catch(System.Data.SqlClient.SqlException e)
- {
- throw new Exception(e.Message);
- }
- finally
- {
- cmd.Dispose();
- connection.Close();
- }
- }
- }
-
-
-
-
-
- public static DataSet Query(string SQLString)
- {
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- DataSet ds = new DataSet();
- try
- {
- connection.Open();
- SqlDataAdapter command = new SqlDataAdapter(SQLString,connection);
- command.Fill(ds,"ds");
- }
- catch(System.Data.SqlClient.SqlException ex)
- {
- throw new Exception(ex.Message);
- }
- return ds;
- }
-
- }
-
-
- #region 存储过程操作
-
-
-
-
-
-
-
- public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
- {
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- SqlDataReader returnReader;
- connection.Open();
- SqlCommand command = BuildQueryCom
-
- mand( connection,storedProcName, parameters );
- command.CommandType = CommandType.StoredProcedure;
-
- returnReader = command.ExecuteReader();
-
- return returnReader;
- }
- }
- private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)
- {
-
- SqlCommand command = new SqlCommand( storedProcName, connection );
- command.CommandType = CommandType.StoredProcedure;
- foreach (SqlParameter parameter in parameters)
- {
- command.Parameters.Add( parameter );
- }
- return command;
-
- }
- public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
- {
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- DataSet dataSet = new DataSet();
- connection.Open();
- SqlDataAdapter sqlDA = new SqlDataAdapter();
- sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters );
- sqlDA.Fill( dataSet, tableName );
- connection.Close();
-
- return dataSet;
- }
- }
-
- #endregion
- }
- }
posted on
2008-12-05 15:38
cean
阅读(
241)
评论()
编辑
收藏
举报