public bool Insert(Photo photo, Database db, DbTransaction transaction = null)
{
DbCommand command = db.GetStoredProcCommand("usp_PhotoInsert");
photo.PhotoId = Guid.NewGuid();
db.AddInParameter(command, "PhotoId", DbType.Guid, photo.PhotoId);
db.AddInParameter(command, "FileName", DbType.String, photo.FileName);
db.AddInParameter(command, "FilePath", DbType.String, photo.FilePath);
db.AddInParameter(command, "ContextId", DbType.Guid, photo.ContextId);
db.AddInParameter(command, "Description", DbType.String, photo.Description);
db.AddInParameter(command, "ContextTypeId", DbType.Int32, (int)photo.ContextType);
db.AddInParameter(command, "ContextSubTypeId", DbType.Int32, photo.ContextSubTypeId);
db.AddInParameter(command, "PhotoCategoryId", DbType.Int32, (int)photo.PhotoCategory);
db.AddInParameter(command, "IsDeleted", DbType.String, photo.IsDeleted);
db.AddInParameter(command, "CreatedBy", DbType.Guid, photo.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
photo.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
photo.UpdatedDate = photo.CreatedDate;
return true;
}
/// <summary>
/// Instanciates a Admin1 object from the database via the admin1ID
/// </summary>
public Admin1(int admin1ID)
{
db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("AG_GetAdmin1Byadmin1ID");
db.AddInParameter(dbCommand, "admin1ID", DbType.Int32, admin1ID);
//execute the stored procedure
using (IDataReader dr = db.ExecuteReader(dbCommand))
{
ColumnFieldList list = new ColumnFieldList(dr);
if (dr.Read())
{
if (list.IsColumnPresent("admin1ID")) { this._admin1ID = (int)dr["admin1ID"]; }
if (list.IsColumnPresent("CountryID")) { this._countryID = (int)dr["CountryID"]; }
if (list.IsColumnPresent("admin1CD")) { this._admin1CD = (string)dr["admin1CD"]; }
if (list.IsColumnPresent("Name")) { this._name = (string)dr["Name"]; }
}
else
{
throw new Exception("There is no Admin1 in the database with the ID " + admin1ID);
}
dr.Close();
}
}
public bool Insert(Option Option, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_OptionInsert");
db.AddInParameter(command, "Name", DbType.String, Option.Name);
db.AddInParameter(command, "Description", DbType.String, Option.Description);
db.AddInParameter(command, "OptionCategoryId", DbType.Int16, Option.OptionCategoryId);
db.AddInParameter(command, "ParentOptionId", DbType.Int16, Option.ParentOptionId);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, Option.IsDeleted);
db.AddInParameter(command, "IsMultiSelect", DbType.Boolean, Option.IsMultiSelect);
db.AddInParameter(command, "Points", DbType.Int16, Option.Points);
db.AddOutParameter(command, "OptionId", DbType.Int16, 3);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
Option.OptionId = Convert.ToInt16(db.GetParameterValue(command, "OptionId").ToString());
return true;
}
public bool Insert(Student student, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_StudentInsert");
db.AddInParameter(command, "StudentId", DbType.Guid, Guid.NewGuid());
db.AddInParameter(command, "UserId", DbType.Guid, student.StudentUser.UserId);
db.AddInParameter(command, "SchoolId", DbType.Guid, student.School.SchoolId);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, student.IsDeleted);
db.AddInParameter(command, "Year", DbType.String, student.School.Year);
db.AddInParameter(command, "StartYear", DbType.String, student.StartYear);
db.AddInParameter(command, "StartMonth", DbType.String, student.StartMonth);
db.AddInParameter(command, "Status", DbType.String, student.Status);
db.AddInParameter(command, "PreviousSchoolInfo", DbType.String, student.PreviousSchoolInfo);
db.AddInParameter(command, "PreviousSchool", DbType.String, student.PreviousSchool);
db.AddInParameter(command, "MajorId", DbType.Int16, student.MajorId);
db.AddInParameter(command, "CreatedBy", DbType.Guid, student.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
db.ExecuteNonQuery(command, transaction);
student.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
student.UpdatedDate = student.CreatedDate;
return true;
}
public bool DeleteRoleRightsByRoleId(Role roles, Database db, DbTransaction transaction)
{
DbCommand dbCommand = db.GetStoredProcCommand("usp_RoleRightDelete");
db.AddInParameter(dbCommand, "RoleId", DbType.Guid, roles.RoleId);
db.ExecuteNonQuery(dbCommand, transaction);
return true;
}
public bool Insert(PartialUser partialUser, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_PartialUserInsert");
partialUser.PartialUserId = Guid.NewGuid();
db.AddInParameter(command, "PartialUserId", DbType.Guid, partialUser.PartialUserId);
db.AddInParameter(command, "Email", DbType.String, partialUser.Email);
db.AddInParameter(command, "FirstName", DbType.String, partialUser.FirstName);
db.AddInParameter(command, "MiddleName", DbType.String, partialUser.MiddleName);
db.AddInParameter(command, "LastName", DbType.String, partialUser.LastName);
db.AddInParameter(command, "Contact", DbType.String, partialUser.Contact);
db.AddInParameter(command, "RoleId", DbType.Guid, partialUser.RoleId);
db.AddInParameter(command, "UserId", DbType.Guid, partialUser.UserId);
db.AddInParameter(command, "PartialHouseId", DbType.Guid, partialUser.PartialHouseId);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, partialUser.IsDeleted);
db.AddInParameter(command, "CreatedBy", DbType.Guid, partialUser.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
db.ExecuteNonQuery(command, transaction);
partialUser.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
partialUser.UpdatedDate = partialUser.CreatedDate;
return true;
}
public DatabaseManager()
{
Chapter09SectionGroup chapter09Config = Chapter09Configuration.GetConfig();
Chapter09Section section = chapter09Config.Chapter09Section;
db = DatabaseFactory.CreateDatabase(section.ConnectionStringName);
}
public TransactionManager(string connectionString)
{
this._transactionOpen = false;
this.connectionString = connectionString;
this.database = new SqlDatabase(connectionString);
this.connection = this.database.CreateConnection();
}
/// <summary>
/// GetItem SAS_ExportData Data...
/// <summary>
/// <param name=sender></param>
/// <param name= e></param>
public ExportDataEN GetItem(ExportDataEN argEn)
{
ExportDataEN loItem = new ExportDataEN();
string sqlCmd = "Select * FROM SAS_ExportData WHERE InterfaceID = @InterfaceID";
Microsoft.Practices.EnterpriseLibrary.Data.Database coDb = DatabaseFactory.CreateDatabase(csConnectionStr);
try
{
using (DbCommand cmd = coDb.GetSqlStringCommand(sqlCmd))
{
coDb.AddInParameter(cmd, "@InterfaceID", DbType.String, argEn.InterfaceID);
using (IDataReader loReader = coDb.ExecuteReader(cmd))
{
if (loReader != null)
{
loReader.Read();
loItem = LoadObject(loReader);
}
loReader.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(loItem);
}
/// <summary>
/// Getlist SAS_ExportData Data...
/// <summary>
/// <param name=sender></param>
/// <param name= e></param>
public List <ExportDataEN> GetList(ExportDataEN argEn)
{
List <ExportDataEN> loEnList = new List <ExportDataEN>();
argEn.InterfaceID = argEn.InterfaceID.Replace("*", "%");
string sqlCmd = "select * from SAS_ExportData where Interfaceid <> '0'";
if (argEn.InterfaceID.Length != 0)
{
sqlCmd = sqlCmd + " and Interfaceid like '" + argEn.InterfaceID + "'";
}
Microsoft.Practices.EnterpriseLibrary.Data.Database coDb = DatabaseFactory.CreateDatabase(csConnectionStr);
try
{
using (DbCommand cmd = coDb.GetSqlStringCommand(sqlCmd))
{
using (IDataReader loReader = coDb.ExecuteReader(cmd))
{
while (loReader.Read())
{
ExportDataEN loItem = LoadObject(loReader);
loEnList.Add(loItem);
}
loReader.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(loEnList);
}
public TransactionDataAccess()
{
//
// TODO: 在此处添加构造函数逻辑
//
db = DatabaseFactory.CreateDatabase();
}
public SimulatedLdapProfileStore(Database database)
{
this.accessor = database.CreateSprocAccessor<ProfileStoreData>("aexpense_ProfileStore_GetProfileFromUser");
this.retryPolicy = RetryPolicyFactory.GetRetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>();
AExpenseEvents.Log.ProfileStoreInitialized();
}
public GradesDataAccess()
{
//
// TODO: 在此处添加构造函数逻辑
//
db = DatabaseFactory.CreateDatabase();
}
public void UpdateWhInfo(Database db, DbTransaction transaction)
{
string sql = "update a set a.wh_name=b.wh_name ,a.wh_code=b.wh_code from wcs_asrv a,wcs_wh b where a.wh_id=b.wh_id and a.asrv_id=@pKeyValue";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "pKeyValue", DbType.Int32, KeyValue);
db.ExecuteNonQuery(cmd, transaction);
}
/// <summary>
/// 获取一个DataSet数据集
/// </summary>
/// <param name="cmdType">命令类型,SQL文本或存储过程</param>
/// <param name="cmdText">SQL语句或存储过程名称</param>
/// <param name="paras">查询参数</param>
/// <returns>DataSet数据集</returns>
public static DataSet ExecuteDataSet(Database db, DbTransaction dbTransaction, CommandType cmdType, string cmdText, params DbParameter[] paras)
{
DbCommand dbcmd = GetDbCommand(db, cmdType, cmdText);
PrepareDbCommand(dbcmd, paras);
DataSet ds = db.ExecuteDataSet(dbcmd, dbTransaction);
return ds;
}
/// <summary>
/// 执行查询语句，返回DataTable
/// </summary>
/// <param name="dc">查询语句</param>
/// <param name="db">操作目标数据库</param>
/// <returns>DataTable</returns>
public static DataTable DataTableQuery(DbCommand dc, Database db)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
try
{
PrepareCommand(ref dc, db);
ds = db.ExecuteDataSet(dc);
if (ds.Tables.Count > 0)
{
dt = ds.Tables[0];
ds.Dispose();
ds = null;
}
else
{
ds.Dispose();
ds = null;
return null;
}
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
return dt;
}
/// <summary>
/// 加载参数
/// </summary>
public static void BuildDBParameter(Database db, DbCommand dbCommand, params SqlParameter[] cmdParms)
{
foreach (SqlParameter sp in cmdParms)
{
db.AddInParameter(dbCommand, sp.ParameterName, sp.DbType,sp.Value);
}
}
public void SetUp()
{
EnvironmentHelper.AssertOracleClientIsInstalled();
DatabaseProviderFactory factory = new DatabaseProviderFactory(OracleTestConfigurationSource.CreateConfigurationSource());
db = factory.Create("OracleTest");
CreateStoredProcedure();
}
public SMSInfoDA()
{
//
// TODO: 在此处添加构造函数逻辑
//
db = DatabaseFactory.CreateDatabase();
}
public static int Insert(Database db, DefaultCardOut defaultCardOut)
{
StringBuilder sbField = new StringBuilder();
StringBuilder sbValue = new StringBuilder();
sbField.Append("INSERT INTO DefaultCardOut(");
sbValue.Append("values(");
sbField.Append("CameraId,");
sbValue.AppendFormat("{0},", defaultCardOut.CameraId);
sbField.Append("DisplayChannelId,");
sbValue.AppendFormat("{0},", defaultCardOut.DisplayChannelId);
sbField.Append("DispalySplitScreenNo)");
sbValue.AppendFormat("{0})", defaultCardOut.DisplaySplitScreenNo);
string cmdText = sbField.ToString() + " " + sbValue.ToString() + "";
try
{
return db.ExecuteNonQuery(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public void SetUp()
{
TestConnectionString testConnection = new TestConnectionString();
testConnection.CopyFile();
db = new SqlCeDatabase(testConnection.ConnectionString);
baseFixture = new DataAccessTestsFixture(db);
}
public static int Insert(Database db, CameraIconInfo cameraIconInfo)
{
StringBuilder sbField = new StringBuilder();
StringBuilder sbValue = new StringBuilder();
sbField.Append("INSERT INTO CameraIconInfo(");
sbValue.Append("values(");
sbField.Append("CameraId");
sbValue.AppendFormat("{0}", cameraIconInfo.CameraId);
sbField.Append(",IconIndex");
sbValue.AppendFormat(",{0}", cameraIconInfo.IconIndex);
sbField.Append(",ToolTip");
sbValue.AppendFormat(",'{0}'", cameraIconInfo.ToolTip);
sbField.Append(",X");
sbValue.AppendFormat(",{0}", cameraIconInfo.X);
sbField.Append(",Y");
sbValue.AppendFormat(",{0}", cameraIconInfo.Y);
sbField.Append(",Map");
sbValue.AppendFormat(",{0}", cameraIconInfo.Map);
sbField.Append(",MatchAlarmId)");
sbValue.AppendFormat(",{0})", cameraIconInfo.MatchAlarmId);
string cmdText = sbField + " " + sbValue;
try
{
return db.ExecuteNonQuery(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public static int Insert(Database db, Track oTrack)
{
StringBuilder sbField = new StringBuilder();
StringBuilder sbValue = new StringBuilder();
sbField.Append("INSERT INTO [Track](");
sbValue.Append("values (");
//sbField.Append("[Id]");
//sbValue.AppendFormat("'{0}'", oTrack.Id);
sbField.Append("[REct])");
sbValue.AppendFormat("'{0}')", oTrack.REct);
string cmdText = sbField.ToString() + " " + sbValue.ToString();
try
{
cmdText = cmdText.Replace("\r\n", "");
db.ExecuteNonQuery(CommandType.Text, cmdText);
int id = int.Parse(db.ExecuteScalar(CommandType.Text, "SELECT ident_current('Track')").ToString());
return id;
}
catch (Exception ex)
{
throw ex;
}
}
public bool Insert(StudentHouseLeave studentHouseLeave, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_StudentHouseLeaveInsert");
db.AddInParameter(command, "HouseId", DbType.Guid, studentHouseLeave.HouseId);
db.AddInParameter(command, "BaseHouseRoomId", DbType.Guid, studentHouseLeave.BaseHouseRoomId);
db.AddInParameter(command, "RequestBy", DbType.Guid, studentHouseLeave.RequestBy);
db.AddInParameter(command, "RequestTo", DbType.Guid, studentHouseLeave.RequestTo);
db.AddInParameter(command, "status", DbType.Int16, studentHouseLeave.status);
db.AddOutParameter(command, "RequestDate", DbType.DateTime, 30);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
studentHouseLeave.RequestDate = Convert.ToDateTime(db.GetParameterValue(command, "RequestDate").ToString());
studentHouseLeave.ResponseDate = studentHouseLeave.RequestDate;
return true;
}
//Get XML From dataBase 1
public static string GetNewXml(int id)
{
string RetrivedXML = null;
try
{
//Instance for the database
DatabaseProviderFactory factory = new DatabaseProviderFactory();
Microsoft.Practices.EnterpriseLibrary.Data.Database db1 = factory.Create("DB1");
DbCommand dbCommand = db1.GetSqlStringCommand("SELECT xmldata FROM data WHERE id='" + id + "'");
object getNewXml = db1.ExecuteScalar(dbCommand);
if (getNewXml != null)
{
RetrivedXML = (string)getNewXml;
}
else
{
throw new Exception(String.Format("Could not find the historyId.", id));
}
return(RetrivedXML);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Initializes a new instance of the <see cref="TransactionManager"/> class.
/// </summary>
/// <param name="connectionString">The connection string to the database.</param>
public TransactionManager(string connectionString)
{
this.connectionString = connectionString;
this.database = new SqlDatabase(connectionString);
//this.database = DatabaseFactory.CreateDatabase(this.instanceName);
this.connection = this.database.CreateConnection();
}
/// <summary>
/// Creates a return parameter and inserts into the command list
/// </summary>
/// <param name="db">The database object</param>
/// <param name="cmd">The command object</param>
/// <remarks>The DBCommand parameter argument that is modified</remarks>
public static void CreateReturnParameter(Database db, ref DbCommand cmd)
{
#region Defensive
#region db
Debug.Assert(db != null, "The argument supplied for parameter 'db' should not be null");
if(db == null)
{
throw new ArgumentNullException("db", "The argument supplied for parameter 'db' should not be null");
}
#endregion
#region cmd
Debug.Assert(cmd != null, "The argument supplied for parameter 'cmd' should not be null");
if(cmd == null)
{
throw new ArgumentNullException("cmd", "The argument supplied for parameter 'cmd' should not be null");
}
#endregion
#endregion
db.AddParameter(cmd, RETURN_VALUE, DbType.Int32, sizeof(int), ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null);
}
/// <summary>
/// 为储存过程加载输出变量参数
/// </summary>
/// <param name="db">database</param>
/// <param name="dbCommand">db command</param>
/// <param name="cmdParms">参数数组</param>
public static void BuildDBParameterForReturnValue(Database db, DbCommand dbCommand, params IDataParameter[] cmdParms)
{
foreach (SqlParameter sp in cmdParms)
{
db.AddParameter(dbCommand, sp.ParameterName, sp.DbType, sp.Size, ParameterDirection.Output, false, 0, 0, sp.SourceColumn, DataRowVersion.Current, DBNull.Value);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="TransactionManager"/> class.
/// </summary>
/// <param name="connectionString">The connection string to the database.</param>
/// <param name="providerInvariantName">Name of the provider invariant.</param>
public TransactionManager( string connectionString, string providerInvariantName )
{
this._connectionString = connectionString;
this._invariantProviderName = providerInvariantName;
this._database = new GenericDatabase(_connectionString, DbProviderFactories.GetFactory( this._invariantProviderName ) );
this._connection = this._database.CreateConnection();
}
/*********************************************************************************************/
/// <summary>
/// Lists Chinese language specific assets
/// </summary>
public static void ListAssets_Chi()
{
string strAssetServer, strFilePath;
StreamWriter oListWriter;
int intSiteId;
strAssetServer = System.Configuration.ConfigurationSettings.AppSettings["AssetServer"];
intSiteId = Convert.ToInt32(System.Configuration.ConfigurationSettings.AppSettings["SiteCodeID"]);
strFilePath = System.Configuration.ConfigurationSettings.AppSettings["FilePath"];
DataLibrary.Database oSiteWideDB = null;
oSiteWideDB = DataLibrary.DatabaseFactory.CreateDatabase("FlukeSitewide");
SqlDataReader sqlReader;
sqlReader = (SqlDataReader)oSiteWideDB.ExecuteReader(System.Data.CommandType.Text, "RTK_Asset_Sel 'chi'," + intSiteId + "");
oListWriter = File.CreateText(strFilePath + "AssetList_zh.htm");
oListWriter.WriteLine("<html>");
oListWriter.WriteLine("<body>");
while (sqlReader.Read())
{
oListWriter.WriteLine("<a href='" + strAssetServer + "/" + sqlReader["Site_Code"] + "/" + sqlReader["File_Name"] + "'>" + strAssetServer + "/" + sqlReader["Site_Code"] + "/" + sqlReader["File_Name"] + "</a><br>");
}
oListWriter.WriteLine("</body>");
oListWriter.WriteLine("</html>");
oListWriter.Close();
sqlReader.Close();
}
public static int Delete(Database db, int id)
{
try
{
StringBuilder sb = new StringBuilder();
DataSet dataSet = GetVideoInfoById(db, id);
if (dataSet.Tables[0].Rows.Count == 0)
{
return 0;
}
//删除相应的文件
VideoInfo videoInfo;
foreach (DataRow dr in dataSet.Tables[0].Rows)
{
videoInfo = new VideoInfo(dr);
if (File.Exists(videoInfo.FilePath))
{
File.Delete(videoInfo.FilePath);
}
}
//删除出数据库中的记录
sb.Append("delete from VideoInfo ");
sb.AppendFormat(" where Id={0}", id);
string cmdText = sb.ToString();
return db.ExecuteNonQuery(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
protected int Insert(BaseTable nesne, DbCommand dbComm, DbTransaction dbTransaction, Database db)
{
//YetkiKullanici ui = (YetkiKullanici)HttpContext.Current.Session["YetkiKullanici"];
//if (ui == null)
//{
// ui = new YetkiKullanici();
// ui.Tckimlikno = "Everyone";
// ui.Ip = HttpContext.Current.Request.UserHostAddress;
//}
int sonuc = 0;
string geriDeger = "";
if (dbTransaction == null) geriDeger = db.ExecuteScalar(dbComm).ToString();
else geriDeger = db.ExecuteScalar(dbComm, dbTransaction).ToString();
//if (ConfigurationSettings.AppSettings.Get("DBLOG_AKTIF") != null &&
// ConfigurationSettings.AppSettings.Get("DBLOG_AKTIF") == "E")
//{
// try
// {
// int.TryParse(geriDeger, out sonuc);
// GenDbLog log = new GenDbLog();
// log.Tckimlikno = ui.Tckimlikno;
// log.KullaniciIp = ui.Ip;
// log.Tablo = nesne.GetName();
// log.KayitId = sonuc;
// log.Kayit = nesne.ToString();
// log.Tarih = DateTime.Now;
// log.YapilanIslem = "I";
// InsertLog(log, dbTransaction, db);
// }
// catch (Exception ex) { }
//}
bool snc = int.TryParse(geriDeger, out sonuc);
return sonuc;
}
public bool Update(OptionItem OptionItem, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_OptionItemUpdate");
db.AddInParameter(command, "OptionItemId", DbType.String, OptionItem.OptionItemId);
db.AddInParameter(command, "Name", DbType.String, OptionItem.Name);
db.AddInParameter(command, "Description", DbType.String, OptionItem.Description);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, OptionItem.IsDeleted);
db.AddInParameter(command, "OptionId", DbType.Int16, OptionItem.OptionId);
db.AddInParameter(command, "UpdatedBy", DbType.Guid, OptionItem.UpdatedBy);
db.AddOutParameter(command, "UpdatedDate", DbType.DateTime, 30);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
OptionItem.UpdatedDate = Convert.ToDateTime(db.GetParameterValue(command, "UpdatedDate").ToString());
return true;
}
protected int Delete(BaseTable nesne, DbCommand dbComm, DbTransaction dbTransaction, Database db)
{
//YetkiKullanici ui = (YetkiKullanici)HttpContext.Current.Session["YetkiKullanici"];
//if (ui == null)
//{
// ui = new YetkiKullanici();
// ui.Tckimlikno = "Everyone";
// ui.Ip = HttpContext.Current.Request.UserHostAddress;
//}
int sonuc = -1;
//if (dbTransaction == null) sonuc = int.Parse(db.ExecuteNonQuery(dbComm).ToString());
//else sonuc = int.Parse(db.ExecuteNonQuery(dbComm, dbTransaction).ToString());
//if (ConfigurationSettings.AppSettings.Get("DBLOG_AKTIF") != null &&
// ConfigurationSettings.AppSettings.Get("DBLOG_AKTIF") == "E" && sonuc > -1)
//{
// try
// {
// GenDbLog log = new GenDbLog();
// log.Tckimlikno = ui.Tckimlikno;
// log.KullaniciIp = ui.Ip;
// log.Tablo = nesne.GetName();
// log.KayitId = (int)nesne.PrimaryKey;
// log.Kayit = "SİLİNDİ";
// log.Tarih = DateTime.Now;
// log.YapilanIslem = "D";
// InsertLog(log, dbTransaction, db);
// }
// catch (Exception ex) { }
//}
return sonuc;
}
protected virtual void AddParametersFromCache(DbCommand command, Database database)
{
foreach (IDataParameter parameter in this.cache.GetCachedParameterSet(database.ConnectionString, command))
{
command.Parameters.Add(parameter);
}
}
public static bool Insert(SurveyEntity survey, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_SurveyInsert");
db.AddInParameter(command, "MyUniversity", DbType.String, survey.MyUniversity);
db.AddInParameter(command, "UniversityName", DbType.String, survey.UniversityName);
db.AddInParameter(command, "UniversityAddress", DbType.String, survey.UniversityAddress);
db.AddInParameter(command, "TypeOfResidence", DbType.String, survey.TypeOfResidence);
db.AddInParameter(command, "TypeOfResidenceOption", DbType.String, survey.TypeOfResidenceOption);
db.AddInParameter(command, "NameOfResidence", DbType.String, survey.NameOfResidence);
db.AddInParameter(command, "AddressOfResidence", DbType.String, survey.AddressOfResidence);
db.AddInParameter(command, "PropertyOwnerComment", DbType.String, survey.PropertyOwnerComment);
db.AddInParameter(command, "Email", DbType.String, survey.Email);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
return true;
}
/// <summary>
/// Delete SAS_ExportData Data...
/// <summary>
/// <param name=sender></param>
/// <param name= e></param>
public bool Delete(ExportDataEN argEn)
{
bool lbRes = false;
string sqlCmd = "DELETE FROM SAS_ExportData WHERE InterfaceID = @InterfaceID";
Microsoft.Practices.EnterpriseLibrary.Data.Database coDb = DatabaseFactory.CreateDatabase(csConnectionStr);
try
{
using (DbCommand cmd = coDb.GetSqlStringCommand(sqlCmd))
{
coDb.AddInParameter(cmd, "@InterfaceID", DbType.String, argEn.InterfaceID);
int liRowAffected = coDb.ExecuteNonQuery(cmd);
if (liRowAffected > -1)
{
lbRes = true;
}
else
{
throw new Exception("Deletion Failed! No Row has been deleted...");
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lbRes);
}
protected void initialize()
{
connectionStringName = ConfigurationManager.ConnectionStrings["BookCheckINOUTDBConnection"].ToString();
_db = new SqlDatabase(connectionStringName);
// _db = DatabaseFactory.CreateDatabase();
}
public void ChangeUserInfo(string userName, string firstName, string lastName, int deptId)
{
Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase();
this.ValidateParam("userName", userName);
DbCommand dbCommand = db.GetStoredProcCommand("ChangePersonalInformation", userName, firstName, lastName, deptId);
db.ExecuteNonQuery(dbCommand);
}
public VinagermanDatabase()
{
// Configure the DatabaseFactory to read its configuration from the .config file
DatabaseProviderFactory factory = new DatabaseProviderFactory();
// Create a Database object from the factory using the connection string name.
_database = factory.Create(CONNECTION_STRING);
}
public static int ExecuteNonQuery(EntLib.Database db, DbCommand cmd)
{
var f = new Func <int>(() =>
{
return(db.ExecuteNonQuery(cmd));
});
return(RetryIt(f, cmd));
}
public static IDataReader ExecuteReader(EntLib.Database db, DbCommand cmd)
{
var f = new Func <IDataReader>(() =>
{
return(db.ExecuteReader(cmd));
});
return(RetryIt(f, cmd));
}
public static DataSet ExecuteDataSet(EntLib.Database db, DbCommand cmd)
{
var f = new Func <DataSet>(() =>
{
return(db.ExecuteDataSet(cmd));
});
return(RetryIt(f, cmd));
}
public static object ExecuteScalar(EntLib.Database db, DbCommand cmd)
{
var f = new Func <object>(() =>
{
return(db.ExecuteScalar(cmd));
});
return(RetryIt(f, cmd));
}
/// <summary>
/// Method to Get AutoNumber
/// </summary>
/// <param name="Description">Description as Input</param>
/// <returns>Returns AutoNumber</returns>
public string GetAutoNumber(string Description)
{
string AutoNo = "";
int CurNo = 0;
int NoDigit = 0;
int AutoCode = 0;
int i = 0;
string SqlStr;
SqlStr = "select * from SAS_AutoNumber where SAAN_Des='" + Description + "'";
try
{
Microsoft.Practices.EnterpriseLibrary.Data.Database loDbAut = DatabaseFactory.CreateDatabase(csConnectionStr);
DbCommand cmd = loDbAut.GetSqlStringCommand(SqlStr);
IDataReader loReader = loDbAut.ExecuteReader(cmd);
if (loReader.Read())
{
AutoCode = Convert.ToInt32(loReader["SAAN_Code"]);
CurNo = Convert.ToInt32(loReader["SAAN_CurNo"]) + 1;
NoDigit = Convert.ToInt32(loReader["SAAN_NoDigit"]);
AutoNo = Convert.ToString(loReader["SAAN_Prefix"]);
if (CurNo.ToString().Length < NoDigit)
{
while (i < NoDigit - CurNo.ToString().Length)
{
AutoNo = AutoNo + "0";
i = i + 1;
}
AutoNo = AutoNo + CurNo;
}
loReader.Close();
}
AutoNumberEn loItem = new AutoNumberEn();
loItem.SAAN_Code = AutoCode;
AutoNumberDAL cods = new AutoNumberDAL();
cods.GetItem(loItem);
loItem.SAAN_Code = Convert.ToInt32(AutoCode);
loItem.SAAN_CurNo = CurNo;
loItem.SAAN_AutoNo = AutoNo;
cods.Update(loItem);
return(AutoNo);
}
catch (Exception ex)
{
Console.Write("Error in connection : " + ex.Message);
return(ex.ToString());
}
}
public void GetModuloHabitacional(string ModuloHabitacionalID)
{
DatabaseProviderFactory databaseProviderFactory = new DatabaseProviderFactory();
Microsoft.Practices.EnterpriseLibrary.Data.Database database = databaseProviderFactory.Create("DefaultConnection");
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine("SELECT Nombre FROM TblModulosHabitacionales WHERE ModuloHabitacional=@PModuloHabitacional");
DbCommand command = database.GetSqlStringCommand(stringBuilder.ToString());
database.AddInParameter(command, "PModuloHabitacional", System.Data.DbType.String, ModuloHabitacionalID);
Nombre = database.ExecuteScalar(command).ToString();
}
public void GetTiemposDeAlimentacion(int TiemposDeAlimentacionID)
{
DatabaseProviderFactory databaseProviderFactory = new DatabaseProviderFactory();
Microsoft.Practices.EnterpriseLibrary.Data.Database database = databaseProviderFactory.Create("DefaultConnection");
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine("SELECT Nombre FROM TblTiemposDeAlimentacion WHERE TiemposDeAlimentacionID=@PTiemposDeAlimentacionID");
DbCommand command = database.GetSqlStringCommand(stringBuilder.ToString());
database.AddInParameter(command, "PTiemposDeAlimentacionID", System.Data.DbType.String, TiemposDeAlimentacionID);
Nombre = database.ExecuteScalar(command).ToString();
}
/// <summary>
/// Descripción: Método para obtener todos los registros de la base de datos en un DataSet. Generalmente este obtiene solo los registros que no estan dados de baja.
/// </summary>
/// <param name="pdb">Instancia de la Base de Datos</param>
/// <returns>Devuelve un objeto DataSet con la coleccion de los registros obtenidos en la consulta.</returns>
public System.Data.DataSet m_Load(Microsoft.Practices.EnterpriseLibrary.Data.Database pdb)
{
try
{
DbCommand oCmd = pdb.GetStoredProcCommand("svc_Tblcodigopostal");
DataSet ds = pdb.ExecuteDataSet(oCmd);
return(ds);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 数据库主键查询（使用微软企业库）
/// </summary>
/// <param name="pdata"></param>
/// <returns></returns>
public static object GetById(Microsoft.Practices.EnterpriseLibrary.Data.Database db, object pdata)
{
//var db = DBFactory.CreateDefault();
PropertyInfo[] pro = pdata.GetType().GetProperties();//获取传来的对象的属性
List <PropertyInfo> idlist = GetIdProperty(pro);
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
StringBuilder sql = new StringBuilder();
StringBuilder sqlend = new StringBuilder();
string table = FindPropertyInfoValue(pdata, "TableName").ToString();
string sqltext = string.Empty;
sql.AppendFormat("select * from {0} where", table);
foreach (PropertyInfo item in idlist)
{
if (item.Name == "TableName")
{
continue;
}
else
{
sql.Append(" " + item.Name + " = '" + item.GetValue(pdata, null) + "'");
}
}
sqltext = sql.ToString();
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqltext;
DbDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
dr.Read();
foreach (PropertyInfo item in pro)
{
if (item.Name == "TableName")
{
continue;
}
else
{
if (string.IsNullOrEmpty(dr[item.Name].ToString()))
{
continue;
}
item.SetValue(pdata, dr[item.Name], null);
}
}
}
}
return(pdata);
}
/// <summary>
/// 数据库更新方法（使用微软企业库）
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static int Update(Microsoft.Practices.EnterpriseLibrary.Data.Database db, object obj)
{
//var db = DBFactory.CreateDefault();
int n = 0;
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
StringBuilder sql = new StringBuilder();
StringBuilder sqlend = new StringBuilder();
//获取对象的属性数组
PropertyInfo[] pro = obj.GetType().GetProperties();
//主键属性数组
List <PropertyInfo> idlist = GetIdProperty(pro);
//要更新的数据表
string table = FindPropertyInfoValue(obj, "TableName").ToString();
//执行的sql语句
string sqltext = string.Empty;
//UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值 and 列2=某值
sql.Append("UPDATE " + table + " set");
sqlend.Append("WHERE");
//拼接sql语句主体
foreach (PropertyInfo item in pro)
{
if (item.Name == "TableName")
{
continue;
}
else
{
sql.Append(" " + item.Name + "= " + item.GetValue(obj, null));
}
}
//根据主键增加定位条件
foreach (PropertyInfo item in idlist)
{
sqlend.Append(" " + item.Name + "= '" + item.GetValue(obj, null) + ", and ");
}
string start = sql.ToString();
start = start.Substring(0, start.Length - 1) + " ";
string end = sqlend.ToString();
end = end.Substring(0, end.Length - 5) + " ";
sqltext = start + end;
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqltext;
n = cmd.ExecuteNonQuery();
}
}
return(n);
}
/// <summary>
/// Executes the <paramref name="dbCommand"/> and returns an <see cref="IDataReader"/> through which the result can be read.
/// It is the responsibility of the caller to close the connection and reader when finished.
/// </summary>
/// <param name="database">The database to execute the command within.</param>
/// <param name="dbCommand">The command that contains the query to execute.</param>
/// <returns>An <see cref="IDataReader"/> object.</returns>
/// <exception cref="System.Exception">The command could not be executed.</exception>
/// <exception cref="System.Data.Common.DbException">The command could not be executed.</exception>
public static IDataReader ExecuteReader(Enterprise.Database database, DbCommand dbCommand)
{
IDataReader results = null;
try
{
results = database.ExecuteReader(dbCommand);
}
catch (Exception ex)
{
throw new BusinessException(ex);
}
return(results);
}
/// <summary>
/// Executes the <paramref name="dbCommand"/> and returns the number of rows affected.
/// </summary>
/// <param name="database">The database to execute the command within.</param>
/// <param name="dbCommand">The command that contains the query to execute.</param>
/// <returns>The number of rows affected.</returns>
/// <exception cref="System.Exception">The command could not be executed.</exception>
/// <exception cref="System.Data.Common.DbException">The command could not be executed.</exception>
public static int ExecuteNonQuery(Enterprise.Database database, DbCommand dbCommand)
{
int results = 0;
try
{
results = database.ExecuteNonQuery(dbCommand);
}
catch (Exception ex)
{
throw new BusinessException(ex);
}
return(results);
}
/// <summary>
/// Executes the <paramref name="dbCommand"/> and returns the results in a new <see cref="DataSet"/>.
/// </summary>
/// <param name="database">The database to execute the command within.</param>
/// <param name="dbCommand">The command that contains the query to execute.</param>
/// <returns>A <see cref="DataSet"/> containing the results of the command.</returns>
/// <exception cref="System.Exception">The command could not be executed.</exception>
/// <exception cref="System.Data.Common.DbException">The command could not be executed.</exception>
public static DataSet ExecuteDataSet(Enterprise.Database database, DbCommand dbCommand)
{
DataSet results = null;
try
{
results = database.ExecuteDataSet(dbCommand);
}
catch (Exception ex)
{
throw new BusinessException(ex);
}
return(results);
}
/// <summary>
/// 通用更新
/// </summary>
/// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
/// <param name="row">DataRow</param>
/// <param name="_table">更新表名称</param>
/// <param name="_identity">主键列名称</param>
/// <returns></returns>
public static int UniversalizationUpdate(Microsoft.Practices.EnterpriseLibrary.Data.Database db, DataRow row, string _table, string _identity)
{
try
{
var cmd = db.DbProviderFactory.CreateCommand();
var sql = new StringBuilder();
var sqlend = new StringBuilder();
sql.Append("UPDATE " + _table + " set");
sqlend.Append("WHERE");
//获取所有的row的属性（获取列集合）
var cols = row.GetType().GetProperties();
foreach (var col in cols)
{
//主键列不更新
if (col.Name == _identity)
{
continue;
}
//获取某列的值
var val = Try2String(GetRowValue(row, col.Name));
//去掉空属性
if (string.IsNullOrEmpty(val))
{
continue;
}
//时间属性初始化时未赋值会变为默认最小值
if (col.PropertyType == typeof(DateTime))
{
DateTime dt;
DateTime.TryParse(val, out dt);
if (dt <= SqlDateTime.MinValue.Value)
{
continue;
}
}
sql.Append(" " + col.Name + " = '" + val + "',");
}
sqlend.Append(" " + _identity + "= '" + Try2String(GetRowValue(row, _identity)) + "'");
var start = sql.ToString();
start = start.Substring(0, start.Length - 1) + " ";
var end = sqlend.ToString();
end = end.Substring(0, end.Length) + " ";
cmd.CommandText = start + end;
return(db.ExecuteNonQuery(cmd));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 通用插入
/// </summary>
/// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
/// <param name="row">DataRow</param>
/// <param name="_table">插入表名称</param>
/// <param name="_isIDENTITY_INSERT_OFF">是否关闭主键列插入功能</param>
/// <param name="_identity">主键列名称</param>
public static int UniversalizationInsert(Microsoft.Practices.EnterpriseLibrary.Data.Database db, DataRow row, string _table, bool _isIDENTITY_INSERT_OFF, string _identity)
{
try
{
var sql = new StringBuilder();
var sqlend = new StringBuilder();
sql.Append("INSERT INTO " + _table + " (");
sqlend.Append(" VALUES (");
var cmd = db.DbProviderFactory.CreateCommand();
//获取所有的row的属性（获取列集合）
var cols = row.GetType().GetProperties();
foreach (var col in cols)
{
//insert去掉主键列插入（仅当数据库IDENTITY_INSERT=OFF时适用；为ON时不用该判断）
if (_isIDENTITY_INSERT_OFF && col.Name == _identity)
{
continue;
}
//获取某列的值
var val = Try2String(GetRowValue(row, col.Name));
//去掉空属性
if (string.IsNullOrEmpty(val))
{
continue;
}
//时间属性初始化时未赋值会变为默认最小值
if (col.PropertyType == typeof(DateTime))
{
DateTime dt;
DateTime.TryParse(val, out dt);
if (dt <= SqlDateTime.MinValue.Value)
{
continue;
}
}
sql.Append(" " + col.Name + ",");
sqlend.Append(" '" + val + "',");
}
var start = sql.ToString();
start = start.Substring(0, start.Length - 1) + ")";
var end = sqlend.ToString();
end = end.Substring(0, end.Length - 1) + ")";
cmd.CommandText = start + end;
return(db.ExecuteNonQuery(cmd));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Executes the <paramref name="dbCommand"/> and returns an <see cref="IDataReader"/> through which the result can be read.
/// It is the responsibility of the caller to close the connection and reader when finished.
/// </summary>
/// <param name="database">The database to execute the command within.</param>
/// <param name="dbCommand">The command that contains the query to execute.</param>
/// <returns>An <see cref="IDataReader"/> object.</returns>
/// <exception cref="System.Exception">The command could not be executed.</exception>
/// <exception cref="System.Data.Common.DbException">The command could not be executed.</exception>
public static object ExecuteScalar(Enterprise.Database database, DbCommand dbCommand)
{
object results = null;
try
{
results = database.ExecuteScalar(dbCommand);
}
catch (Exception ex)
{
throw new BusinessException(ex);
}
return(results);
}
/// <summary>
/// 查询数据库是否有对应记录（是否唯一）
/// </summary>
/// <param name="_db"></param>
/// <param name="_tablename">>注意表名要用数据库全称</param>
/// <param name="_sqlwhere">限定语句，必须and开头，可留空</param>
/// <returns></returns>
public static bool UniversalizationGetUnique(Microsoft.Practices.EnterpriseLibrary.Data.Database _db, string _tablename, string _sqlwhere)
{
try
{
var ds = new DataSet();
var sql = "select * from " + _tablename + " where 1=1 " + _sqlwhere;
var cmd = _db.GetSqlStringCommand(sql);
_db.LoadDataSet(cmd, ds, _tablename);
return(ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0);
}
catch (Exception ex)
{
throw ex;
}
}
public DatabaseHelper(string ConnectionString, bool isSQLConnectionString)
{
_connectionString = ConnectionString;
_providesqlconnectionstring = isSQLConnectionString;
if (!_connectionString.ToLower().Contains("initial"))
{
_connectionString = EncryptDecrypt.EncryptDecrypt.DecryptString(_connectionString);//.Replace("\u0006", "").Replace("\u0010", "");
}
if (db == null)
{
db = GetDatabase();
}
}
/// <summary>
/// Prevent anyone from instantiating this class by making New private
/// </summary>
/// <remarks></remarks>
private DatafilesBackingStore()
{
//GetInstance(Of Database)
try
{
//Create a database from the enterprise library. 'EnterpriseLibrary_Patterns' matches the values in the app.config file:
//<dataConfiguration defaultDatabase="EnterpriseLibrary_Patterns" />
//<connectionStrings><add name="EnterpriseLibrary_Patterns" connectionString=...
store = EnterpriseLibraryContainer.Current.GetInstance <Microsoft.Practices.EnterpriseLibrary.Data.Database>("EnterpriseLibrary_Patterns");
}
catch (Exception ex)
{
throw (ex);
}
}
/// <summary>
/// 根据条件where单表删除记录
/// </summary>
/// <param name="_db"></param>
/// <param name="_tablename">注意表名要用数据库全称</param>
/// <param name="_sqlwhere">限定语句，必须and开头，不能为空！</param>
/// <returns></returns>
public static int UniversalizationDeleteWhere(Microsoft.Practices.EnterpriseLibrary.Data.Database _db, string _tablename, string _sqlwhere)
{
if (string.IsNullOrEmpty(_sqlwhere))
{
return(0); //防止误操作，禁止无条件删除
}
try
{
var sql = "delete from " + _tablename + " where 1=1 " + _sqlwhere;
var cmd = _db.DbProviderFactory.CreateCommand();
cmd.CommandText = sql;
return(_db.ExecuteNonQuery(cmd));
}
catch (Exception ex)
{
throw ex;
}
}
