public abstract class DBHelper
{
public static SqlConnection conn = new SqlConnection("server=longdabing;database=test;uid=sa;pwd=sasa");
public DBHelper()
{
conn.Open();
}
/// <summary>
/// insert，update，delete
/// </summary>
/// <returns></returns>
public abstract int ExecuteNonQuery(string sql);
/// <summary>
/// mulinsert
/// </summary>
/// <param name="insertdata"></param>
/// <returns></returns>
public abstract bool MutiInsertData(DataTable insertdata); /// <summary>
/// 查询select
/// </summary>
/// <returns></returns>
public abstract DataTable ExecuteGetData(string sql); //public abstract IQueryable<ldbEFEntities> ExecuteEFQuery(); /// <summary>
/// 调用存储过程
/// </summary>
/// <param name="procedurename"></param>
/// <returns>返回数据集</returns>
public abstract DataSet ExeStoredProcedure(string procedurename,SqlParameter[] sqlpara);
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procedurename">存储过程名字</param>
/// <param name="sqlpara">参数</param>
/// <returns>返回被影响的行数</returns>
public abstract void ExeStoredProcedure(string procedurename, SqlParameter[] sqlpara,out int rowcount); } /// <summary>
/// sql数据库查询
/// </summary>
public class DBHelperSql : DBHelper
{
public override int ExecuteNonQuery(string sql)
{
int cnt = ;
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
cnt = cmd.ExecuteNonQuery();
cmd.Dispose(); return cnt;
}
catch (Exception)
{
return cnt;
}
finally
{
conn.Close();
}
} public override DataTable ExecuteGetData(string sql)
{
DataTable dt = new DataTable();
try
{
SqlDataAdapter sdd = new SqlDataAdapter(sql, conn);
sdd.Fill(dt);
}
catch (Exception)
{
//return dt;
}
finally
{
conn.Close();
}
return dt;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procedurename"></param>
/// <param name="sqlpara"></param>
/// <returns>返回数据集</returns>
public override DataSet ExeStoredProcedure(string procedurename,SqlParameter[] sqlpara)
{
DataSet ds = new DataSet();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = procedurename;
cmd.CommandType = CommandType.StoredProcedure;
if (sqlpara.Length > )
{
cmd.Parameters.AddRange(sqlpara);
}
//cmd.Parameters.Add("@para", SqlDbType.VarChar, 50);
SqlDataAdapter sdd = new SqlDataAdapter(cmd);
sdd.Fill(ds);
}
catch (Exception)
{
//return dt;
}
finally
{
conn.Close();
}
return ds;
} public override void ExeStoredProcedure(string procedurename, SqlParameter[] sqlpara,out int rowcount)
{
rowcount = ;
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = procedurename;
cmd.CommandType = CommandType.StoredProcedure;
//SqlParameter para = new SqlParameter();
//para.TypeName = "";
if (sqlpara.Length > )
{
cmd.Parameters.AddRange(sqlpara);
}
rowcount = cmd.ExecuteNonQuery();
}
catch (Exception)
{
//return dt;
}
finally
{
conn.Close();
}
} public override bool MutiInsertData(DataTable insertdata)
{
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
SqlTransaction sqltran = conn.BeginTransaction();//开启事务
bulkCopy.DestinationTableName = "inforsqlbulk";
bulkCopy.BatchSize = insertdata.Rows.Count; if (conn.State == ConnectionState.Closed)
{
conn.Open();
} //for (int i = 0; i < dt.Columns.Count; i++)
//{
// bulkCopy.ColumnMappings.Add(i, i); //映射定义数据源中的列和目标表中的列之间的关系
//}
try
{
if (insertdata != null && insertdata.Rows.Count > )
{
bulkCopy.WriteToServer(insertdata); }
return true;
}
catch
{
sqltran.Rollback();//回滚。
return false;
}
}
} /// <summary>
/// EntityFramework 查询
/// </summary>
public class DBHelperEF : DBHelper
{
public override int ExecuteNonQuery(string sql)
{
throw new NotImplementedException();
}
public override DataTable ExecuteGetData(string sql)
{
throw new NotImplementedException();
} public override DataSet ExeStoredProcedure(string procedurename, SqlParameter[] sqlpara)
{
throw new NotImplementedException();
} public override void ExeStoredProcedure(string procedurename, SqlParameter[] sqlpara, out int rowcount)
{
throw new NotImplementedException();
} public override bool MutiInsertData(DataTable insertdata)
{
throw new NotImplementedException();
}
}
/// <summary>
/// Access数据库
/// </summary>
public class DBHelperAccess : DBHelper
{
public override int ExecuteNonQuery(string sql)
{
throw new NotImplementedException();
}
public override DataTable ExecuteGetData(string sql)
{
throw new NotImplementedException();
} public override DataSet ExeStoredProcedure(string procedurename, SqlParameter[] sqlpara)
{
throw new NotImplementedException();
} public override void ExeStoredProcedure(string procedurename, SqlParameter[] sqlpara, out int rowcount)
{
throw new NotImplementedException();
} public override bool MutiInsertData(DataTable insertdata)
{
throw new NotImplementedException();
}
} /// <summary>
/// Oracle数据库
/// </summary>
public class DBHelperOracle : DBHelper
{
public override int ExecuteNonQuery(string sql)
{
throw new NotImplementedException();
}
public override DataTable ExecuteGetData(string sql)
{
throw new NotImplementedException();
} public override DataSet ExeStoredProcedure(string procedurename, SqlParameter[] sqlpara)
{
throw new NotImplementedException();
} public override void ExeStoredProcedure(string procedurename, SqlParameter[] sqlpara, out int rowcount)
{
throw new NotImplementedException();
} public override bool MutiInsertData(DataTable insertdata)
{
throw new NotImplementedException();
}
} public abstract class DBHelperFactory
{
public abstract DBHelper CreateDBHelper();
} public class DBHelperSQLFactory : DBHelperFactory
{
public override DBHelper CreateDBHelper()
{
return new DBHelperSql();
}
} public class DBHelperAccessFactory : DBHelperFactory
{
public override DBHelper CreateDBHelper()
{
return new DBHelperAccess();
}
} public class DBHelperEFFactory : DBHelperFactory
{
public override DBHelper CreateDBHelper()
{
return new DBHelperEF();
}
} public class DBHelperOracleFactory : DBHelperFactory
{
public override DBHelper CreateDBHelper()
{
return new DBHelperOracle();
}
}