sqldriver.cs

.net的数据持久层

using System;
using System.Text;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;

namespace Platform.Data {

	public class SqlDriver : IDbDriver {

		public SqlDriver() { }

		public string ParameterPrix {
			get { return "@"; }
		}

		private SqlCommand CreateCommand(string connString, ISqlStatement sql) {
			SqlConnection conn = new SqlConnection( connString );
			SqlCommand cmd = new SqlCommand( sql.SqlClause, conn );
			cmd.CommandTimeout = 300;
			cmd.CommandType = sql.CommandType;
			foreach( DataParameter dp in sql.Parameters ){
				SqlParameter p = new SqlParameter();
				p.ParameterName = "@" + dp.ParameterName;
				p.Value = dp.Value;
				p.Size = dp.Size;
				p.Direction = dp.Direction;
				cmd.Parameters.Add( p );
			}
			conn.Open();
			return cmd;
		}

		public string BuildListSqlCaluse(string sql, int from, int to, string conOrderString,  string oderString){
			string tmpSql = sql.Replace("SELECT", String.Format( "SELECT TOP {0} ", to )); 
			string fmt = "SELECT * FROM ( SELECT TOP {0} * FROM ( SELECT TOP {1} * FROM( {2} ) TABLE__1) TABLE__2 {3} ) TABLE__3 {4} ";
			return String.Format(fmt, to - from, to, tmpSql, conOrderString, oderString );
		}

		public DataSet Query(string connString, ISqlStatement sql) {
			using( SqlCommand cmd = this.CreateCommand( connString, sql )){
				SqlDataAdapter sda = new SqlDataAdapter( cmd );
				DataSet ds = new DataSet();
				sda.Fill( ds );
				cmd.Connection.Close();
				return ds;
			}
		}

		public object QueryScalar(string connString, ISqlStatement sql) {
			using ( SqlCommand cmd = this.CreateCommand( connString , sql ) ){
				object obj = cmd.ExecuteScalar();
				cmd.Connection.Close();
				return obj;
			}
		}

		public int Update(string connString, ISqlStatement sql) {
			try
			{
				using ( SqlCommand cmd = this.CreateCommand( connString , sql ) )
				{
					int ret = cmd.ExecuteNonQuery();
					cmd.Connection.Close();
					return ret;
				}
			}
			catch(Exception ex)
			{
				string kk = ex.Message;
			}
			return 0;
		}
	}
}