public datacontrol(String path)
{
bool flag;
if(File. Exists(path)){
flag=false;
}else{
flag=true;
}
SQLiteConnection con = new SQLiteConnection ("Data Source="+path);
str = path;
con.Open();
connection=con;
cmd=new SQLiteCommand(con);
if(flag){
cmd.CommandText="create table driver(driverid varchar PRIMARY KEY,personid varchar not null,sex bool not null,name varchar not null,birthday char(8) not null,password varchar not null)";
cmd.ExecuteNonQuery();
cmd.CommandText="create table adminstrator (id varchar PRIMARY KEY,password varchar not null)";
cmd.ExecuteNonQuery();
cmd.CommandText="insert into adminstrator values(\'adminstrator\',\'1234\')";
cmd.ExecuteNonQuery();
cmd.CommandText="create table car(carid varchar primary key,caryear int,avaliable bool,insurancen bool,yearcheck bool)";
cmd.ExecuteNonQuery();
cmd.CommandText="create table contract(carid varchar,driverid varchar,startdate int,enddate int,isagree bool,isvalid bool,PRIMARY KEY(carid,driverid,startdate))";
cmd.ExecuteNonQuery();
//TODO create the table
}
}
public void AddFingerprint(FingerprintCard card, Image newprint)
{
using (var newconnect = new DbConnection())
{
if (!newconnect.Connect()) throw new CannotConnectToDatabaseException();
string query;
if (card.FingerprintList.Count == 0) //card is empty => register new card
{
query = "insert into usertbl values(\"" + card.guid + "\",\""
+ card.FirstName + "\",\"" + card.LastName + "\");";
newconnect.SendQueryAndClose(query);
}
query = "insert into fprinttbl values(?File,\""
+ card.guid + "\");";
byte[] rawimg = SerializeImage(newprint);
MySqlParameter pFile = new MySqlParameter("?File", rawimg);
newconnect.SendQueryAndClose(query, pFile);
card.FingerprintList.Add(newprint);
}
CachedDB.UpdateCard(card);
}
public async Task<dynamic> Create(ToDoItem item)
{
try
{
//Reference the Cloudant db.
var myCouch = new DbConnection();
var client = myCouch.GetClient();
// Post/Insert to Cloudant using myCouch
var response = await client.Documents.PostAsync("{\"text\":\"" + item.text + "\"}");
if (response.IsSuccess)
{
return "{\"id\":\"" + response.Id + "\",\"rev\":\"" + response.Rev + "\",\"text\":\"" + item.text + "\"}";
}
else
{
return "{\"msg\": \"Failure to POST. Status Code: " + response.StatusCode + ". Reason: " + response.Reason + "\"}";
}
}
catch (Exception e)
{
return "{\"msg\": \"Failure to POST: " + e + "\"}";
}
}
public static ArrayList retrieveConversation(LoggedUser user, Host host)
{
ArrayList conversation = new ArrayList(new ArrayList());
DbConnection db = new DbConnection();
SqlConnection connection = db.OpenConnection();
int conversation_id = 0;
String query = "SELECT id FROM Conversation WHERE host=@host AND loggedUser=@user";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@host", host.loginName);
command.Parameters.AddWithValue("@user", user.loginName);
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
conversation_id = reader.GetInt32(0);
}
db.CloseConnection();
db.OpenConnection();
String query1 = "SELECT id,content,initiator,message_read FROM Message WHERE conversation_id=@id";
SqlCommand command1 = new SqlCommand(query1, connection);
command1.Parameters.AddWithValue("@id", conversation_id);
SqlDataReader reader1 = command1.ExecuteReader();
while (reader1.Read())
{
int id = reader1.GetInt32(0);
String message = reader1.GetString(1);
String initiator = reader1.GetString(2);
int read = reader1.GetInt32(3);
ArrayList array = new ArrayList();
array.Add(message); array.Add(initiator); array.Add(read); array.Add(id);
conversation.Add(array);
}
db.CloseConnection();
return conversation;
}
public DatabaseDriver()
{
this.DatabaseEngine = Config.GetDatabaseEngine();
DbConnectionStringBuilder Builder;
if (this.DatabaseEngine == DatabaseEngine.Sqlite)
{
Builder = new SQLiteConnectionStringBuilder();
string FullPath = Path.Combine(Utils.AssemblyPath, Config.GetType<string>("Database", "Database") + ".sqlite3");
IsNewDatabase = !File.Exists(FullPath) || new FileInfo(FullPath).Length == 0;
Builder.Add("Data Source", FullPath);
Connection = new SQLiteConnection(Builder.ConnectionString);
}
else if (this.DatabaseEngine == DatabaseEngine.Mysql)
{
Builder = new MySqlConnectionStringBuilder();
Builder.Add("Server", Config.GetType<string>("Database", "Hostname"));
Builder.Add("Port", Config.GetType<int>("Database", "Port"));
Builder.Add("User ID", Config.GetType<string>("Database", "Username"));
Builder.Add("Password", Config.GetType<string>("Database", "Password"));
Builder.Add("Database", Config.GetType<string>("Database", "Database"));
Connection = new MySqlConnection(Builder.ConnectionString);
}
else
{
throw new Exception("Invalid Database type.");
}
}
protected void Page_Load(object sender, EventArgs e)
{
//DbConnection 인스턴스 초기화
DbConnection DbConn = new DbConnection();
SqlConnection conn = DbConn.DbConn();
////프로시저명
//string SpName = "SP_BoardList";
//SqlCommand cmd = new SqlCommand(SpName, conn);
//cmd.CommandType = CommandType.StoredProcedure;
////SqlDataReader 사용
//SqlDataReader objDr = cmd.ExecuteReader();
//this.List.DataSource = objDr;
//this.List.DataBind();
//SqlDataAdapter 사용
SqlDataAdapter da = new SqlDataAdapter("SP_BoardList", conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
//DataSet 사용
DataSet ds = new DataSet();
da.Fill(ds, "BoardList");
List.DataSource = ds;
List.DataBind();
}
public async Task<string> update(ToDoItem item)
{
try
{
var dbRef = new DbConnection();
var client = dbRef.GetClient();
//setup updated fields.
var data = "{\"text\":\"" + item.text + "\"}";
//Update
var response = await client.Documents.PutAsync(item.id, item.rev, data);
if (response.IsSuccess)
{
return "{\"id\":\"" + response.Id + "\",\"rev\":\"" + response.Rev + "\",\"text\":\"" + item.text + "\"}";
}
else
{
return "{\"msg\": \"Failure to PUT. Status Code: " + response.StatusCode + ". Reason: " + response.Reason + "\"}";
}
}
catch (Exception e)
{
return "{\"msg\": \"Failure to PUT." + e + "\"}";
}
}
public async Task<dynamic> GetAll()
{
try
{
var dbRef = new DbConnection();
var client = dbRef.GetClient();
//Create the DB if it does not exist.
if (client.Database.GetAsync().Result.Error == "not_found")
{
await dbRef.CreateDB();
//load with initial data
await client.Documents.PostAsync("{\"text\":\"Sample 1\"}");
await client.Documents.PostAsync("{\"text\":\"Sample 2\"}");
await client.Documents.PostAsync("{\"text\":\"Sample 3\"}");
}
//Query for all docs including full content of the docs.
var query = new QueryViewRequest("_all_docs").Configure(query1 => query1.IncludeDocs(true));
//GET
RawResponse response = await client.Views.QueryRawAsync(query);
if (response.IsSuccess)
{
return response.Content;
}
else
{
return "{\"msg\": \"Failure to GET. Status Code: " + response.StatusCode + ". Reason: " + response.Reason + "\"}";
}
}
catch (Exception e)
{
return "{\"msg\": \"Failure to GET: " + e + "\"}";
}
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="Engine">The string name, from the GetDatabaseEngine() method</param>
/// <param name="Host">The Database server IP Address</param>
/// <param name="Port">The Database server Port Number</param>
/// <param name="DatabaseName">The name of the database</param>
/// <param name="User">A username, with database privliages</param>
/// <param name="Pass">The password to the User</param>
public DatabaseDriver(string Engine, string Host, int Port, string DatabaseName, string User, string Pass)
{
// Set class variables, and create a new connection builder
this.DatabaseEngine = GetDatabaseEngine(Engine);
DbConnectionStringBuilder Builder;
if (this.DatabaseEngine == DatabaseEngine.Sqlite)
{
string FullPath = Path.Combine(MainForm.Root, DatabaseName + ".sqlite3");
IsNewDatabase = !File.Exists(FullPath) || new FileInfo(FullPath).Length == 0;
Builder = new SQLiteConnectionStringBuilder();
Builder.Add("Data Source", FullPath);
Connection = new SQLiteConnection(Builder.ConnectionString);
}
else if (this.DatabaseEngine == DatabaseEngine.Mysql)
{
IsNewDatabase = false;
Builder = new MySqlConnectionStringBuilder();
Builder.Add("Server", Host);
Builder.Add("Port", Port);
Builder.Add("User ID", User);
Builder.Add("Password", Pass);
Builder.Add("Database", DatabaseName);
Connection = new MySqlConnection(Builder.ConnectionString);
}
else
{
throw new Exception("Invalid Database type.");
}
}
/// <summary>
/// 构造函数
/// </summary>
public DBHelper(string providerName, string connectionString, string sqlType)
{
this._sqlType = sqlType;
if (string.Compare(sqlType, "Mysql", true) == 0)
{
_connection = new MySqlConnection();
_command = new MySqlCommand();
this._paraPrefix = "?";
}
else
{
_provider = GetDbProvider(providerName);
_connection = _provider.CreateConnection();
_command = _provider.CreateCommand();
if (string.Compare(sqlType, "Oracle", true) == 0)
{
this._paraPrefix = ":";
}
else if (string.Compare(sqlType, "SQLServer", true) == 0)
{
this._paraPrefix = "@";
}
}
_connection.ConnectionString = connectionString;
_command.Connection = _connection;
}
private void BindGrid(DbConnection conn)
{
conn.Open();
_schema = new DataSet();
var schema = conn.GetSchema();
foreach (DataRow dataRow in schema.Rows)
{
var tableName = dataRow["CollectionName"].ToString();
if(!_schema.Tables.Contains(tableName))
{
var dt = conn.GetSchema(tableName);
dt.TableName = tableName;
_schema.Tables.Add(dt);
}
}
conn.Close();
dgSchema.DataSource = _schema.Tables[0];
cbTable.DataSource = _schema.Tables[0];
cbTable.DisplayMember = "CollectionName";
cbTable.ValueMember = "CollectionName";
_previousWidth = dgSchema.Width;
_previousState = WindowState;
}
public static DbParameter[] DeriveParameters(DbConnection dbConnection, string spName)
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConn");
}
if (string.IsNullOrEmpty(spName))
{
throw new ArgumentNullException("spName");
}
DbCommand command = null;
using (DbConnection connection = (DbConnection) ((ICloneable) dbConnection).Clone())
{
command = connection.CreateCommand();
command.CommandText = spName;
command.CommandType = CommandType.StoredProcedure;
connection.Open();
if (!(command is SqlCommand))
{
throw new NotSupportedException();
}
SqlCommandBuilder.DeriveParameters((SqlCommand) command);
connection.Close();
}
if ((command.Parameters.Count > 0) && (command.Parameters[0].Direction == ParameterDirection.ReturnValue))
{
command.Parameters.RemoveAt(0);
}
DbParameter[] array = new DbParameter[command.Parameters.Count];
command.Parameters.CopyTo(array, 0);
return array;
}
protected DBAccessBase()
{
SetConfiguration();
this.dbConnection = dbFactory.CreateConnection();
this.dbConnection.ConnectionString = strConnectionString;
this.dbTransaction = null;
}
private DbCommand CreateCommand(string commandText, DbConnection connection)
{
DbCommand command = factory.CreateCommand();
command.Connection = connection;
command.CommandText = commandText;
return command;
}
public FingerprintCard[] GetFingerprints()
{
FingerprintCard[] resultarray = CachedDB.getCacheDb(); //getCache
if (resultarray != null) return resultarray;
using (var newconnect = new DbConnection())
{
if (!newconnect.Connect()) throw new CannotConnectToDatabaseException();
string query = "select * from usertbl;";
MySqlDataReader reader = newconnect.SendQuery(query);
List<FingerprintCard> fingerprintlist = new List<FingerprintCard>();
while (reader.Read())
{
FingerprintCard newcard = new FingerprintCard(reader.GetValue(1).ToString(), reader.GetValue(2).ToString());
string guidstr = (string)reader.GetValue(0);
newcard.guid = new Guid(guidstr);
newcard.FingerprintList = GetFingerprintList(newcard.guid);
fingerprintlist.Add(newcard);
}
reader.Close();
resultarray = fingerprintlist.ToArray();
}
CachedDB.UpdateCache(resultarray);
return resultarray;
}
public byte[] GetBytes(DbConnection connection)
{
using (var contentStream = GetContentStream(connection))
{
return contentStream.Length == 0 ? null : new BinaryReader(contentStream).ReadBytes((int)contentStream.Length);
}
}
public Boolean saveImageInDb()
{
DbConnection db = new DbConnection();
SqlConnection connection = db.OpenConnection();
String query = "INSERT INTO Images (name, contentType, property, userLogin, data) VALUES (@name, @contentType, @property, @user, @data);";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@name", name);
command.Parameters.AddWithValue("@contentType", contentType);
if (propertyId != -1)
{
command.Parameters.AddWithValue("@property", propertyId);
}
else
{
command.Parameters.AddWithValue("@property", "NULL");
}
command.Parameters.AddWithValue("@user", user);
command.Parameters.AddWithValue("@data", bytes);
try
{
System.Diagnostics.Debug.Write(command.CommandText);
command.ExecuteNonQuery();
db.CloseConnection();
return true;
}
catch (Exception ex)
{
System.Diagnostics.Debug.Write(ex.Message);
db.CloseConnection();
return false;
}
}
public override void DoJob()
{
StringBuilder sb = new StringBuilder(); // Result of the query.
sb.AppendFormat("JobType: {0}", GetName()).AppendLine();
/*
* TODO: Properly read in connection information here.
*/
DbConnectorInfo sourceInfo = new DbConnectorInfo("localhost\\SQLEXPRESS", "NORTHWND", "sa", "Conestoga1");
DbConnection sourceConnection = new DbConnection(sourceInfo, "MS");
Dictionary<string, string> keys = new Dictionary<string, string>();
keys.Add("Customers", "CustomerID");
keys.Add("Orders", "OrderID");
List<string> joins = new List<string>();
joins.Add("full");
sb.Append("Connecting to source...").AppendLine();
sb.AppendFormat("DbConnectorInfo:").AppendLine();
sb.AppendFormat("server: {0}", sourceInfo.server).AppendLine();
sb.AppendFormat("database: {0}", sourceInfo.database).AppendLine();
sb.AppendFormat("userid: {0}", sourceInfo.userid).AppendLine();
/*
* Open the source and destination databases.
*/
if (!sourceConnection.ConnectionOpen)
{
sourceConnection.OpenDBConnection();
}
/*
* Pull data from BioLinks.
* TODO: Figure out wtf this method even takes.
*/
DataTable sourceData = sourceConnection.pullData(keys, joins, null, null);
/*
* Log the query.
*/
foreach (DataRow row in sourceData.Rows)
{
foreach (DataColumn col in sourceData.Columns)
{
sb.AppendFormat("{0} ", row[col]);
}
sb.AppendLine();
}
Logger.logMessage(sb.ToString(), AppDomain.CurrentDomain.BaseDirectory + AppDomain.CurrentDomain.RelativeSearchPath + "\\Events2.txt");
/*
* Close the source and destination databases.
*/
sourceConnection.CloseDBConnection();
}
public static void DeleteQueuesForEndpoint(DbConnection connection, string schema, string endpointName)
{
DeleteQueue(connection, schema, endpointName);
DeleteQueue(connection, schema, $"{endpointName}.{Environment.MachineName}");
DeleteQueue(connection, schema, $"{endpointName}.Retries");
DeleteQueue(connection, schema, $"{endpointName}.Timeouts");
DeleteQueue(connection, schema, $"{endpointName}.TimeoutsDispatcher");
}
public static void EndConn()
{
if (conn != null)
{
conn.Close();
conn = null;
}
}
public StorageSession(DbConnection connection, DbTransaction transaction, bool ownsTransaction)
{
Guard.AgainstNull(nameof(connection), connection);
Guard.AgainstNull(nameof(transaction), transaction);
Connection = connection;
this.ownsTransaction = ownsTransaction;
Transaction = transaction;
}
//connects to SQL Server here
public DAL()
{
conn = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ProviderName).CreateConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ConnectionString;
cmd = conn.CreateCommand();
cmd.Connection = conn;
conn.Open();
cmd.CommandType = CommandType.Text;
}
public void OnBeforeEachTest()
{
DbConnection.Init("ConnectionString");
using (var db = new DbConnection())
{
db.DropCreateTableAsync<Person>().Wait();
}
}
public static DbConnection Instance()
{
if (_connection == null)
{
_connection = new DbConnection();
}
return _connection;
}
protected override string GetDatabaseName(DbConnection dbConnection)
{
var name = base.GetDatabaseName(dbConnection);
if (name.IsNullOrEmpty())
name = Path.GetFileNameWithoutExtension(GetDataSourceName(dbConnection));
return name;
}
public static void Initialize(Config config)
{
conn = new MySqlConnection(
String.Format("Server={0};Port={1};Database={2};Uid={3};Pwd={4};",
config.DatabaseHost,
config.DatabasePort,
config.DatabaseName,
config.DatabaseUser,
config.DatabasePassword));
}
async Task Unsubscribe(Subscriber subscriber, DbConnection connection, MessageType messageType)
{
using (var command = connection.CreateCommand())
{
command.CommandText = subscriptionCommands.Unsubscribe;
command.AddParameter("MessageType", messageType.TypeName);
command.AddParameter("Subscriber", subscriber.TransportAddress);
await command.ExecuteNonQueryEx();
}
}
protected void getView()
{
//DbConnection 인스턴스 초기화
DbConnection DbConn = new DbConnection();
SqlConnection conn = DbConn.DbConn();
//프로시저명
string SpName = "SP_BoardView";
SqlCommand cmd = new SqlCommand(SpName, conn);
cmd.CommandType = CommandType.StoredProcedure;
//파라미터 설정
cmd.Parameters.Add("@PK_BoardList", SqlDbType.Int);
cmd.Parameters["@PK_BoardList"].Value = Convert.ToInt32(Request.QueryString["ID"].ToString());
SqlDataReader objDr = cmd.ExecuteReader();
while(objDr.Read()){
lblKorName.Text = objDr[1].ToString();
lblEngName.Text = objDr[2].ToString();
lblJumin1.Text = objDr[3].ToString().Substring(0, 6);
lblJumin2.Text = objDr[3].ToString().Substring(6);
string BirthdayStr = objDr[4].ToString();
lblBirthday.Text = BirthdayStr.Substring(0, 4) + "년 " + BirthdayStr.Substring(4, 2) + "월 " + BirthdayStr.Substring(6) + "일";
if (objDr[5].ToString().Equals(1))
{
chkLunar.Checked = true;
}
else
{
chkLunar.Checked = false;
}
lblRepNo.Text = objDr[6].ToString().Substring(0, 3);
lblTelMiddleNo.Text = objDr[6].ToString().Substring(3, 4);
lblTelLastNo.Text = objDr[6].ToString().Substring(7);
string sexType = objDr[7].ToString();
if (objDr[7].ToString().Equals("M"))
{
lblSex.Text = sexType.Replace(objDr[7].ToString(), "남");
}
else
{
lblSex.Text = sexType.Replace(objDr[7].ToString(), "여");
}
lblReligion.Text = objDr[8].ToString();
lblMilitaryType.Text = objDr[9].ToString();
txtRemark.Text = objDr[10].ToString();
}
}
public async Task ShouldInsertAnObject()
{
using (var db = new DbConnection())
{
Enumerable.Range(0, 100).ToList().ForEach(_ => db.InsertAsync(new Person {Name = "Rick Bobby"}).Wait());
var people = await db.Select<Person>().SelectAsync();
people.Count().Should().Be(100);
}
}
public async Task ShouldDropCreateATable()
{
using (var db = new DbConnection())
{
await db.DropTableAsync<Table>();
await db.CreateTableAsync<Table>();
await db.DropCreateTableAsync<Table>();
}
}
public static void Configure(DbContextOptionsBuilder <LifeDbContext> builder, DbConnection connection)
{
builder.UseSqlServer(connection);
}
public DbDataReader ExecuteReader(DbCommand command, DbConnection connection)
{
OnExecuteCommand(command);
command.Connection = connection;
return command.ExecuteReader();
}
protected static FR_L5AC_GDSfDID_1214_Array Execute(DbConnection Connection, DbTransaction Transaction, P_L5AC_GDSfDID_1214 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
var returnStatus = new FR_L5AC_GDSfDID_1214_Array();
DbCommand command = Connection.CreateCommand();
command.Connection = Connection;
command.Transaction = Transaction;
var commandLocation = "CL5_MyHealthClub_Actions.Atomic.Retrieval.SQL.cls_Get_DiagnosesStatistics_for_DoctorID.sql";
command.CommandText = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(commandLocation)).ReadToEnd();
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "ticket", securityTicket);
command.CommandTimeout = QueryTimeout;
command.CommandText = System.Text.RegularExpressions.Regex.Replace(command.CommandText, "=[ \t]*@ListDoctorID", " IN $$ListDoctorID$$");
CSV2Core_MySQL.Support.DBSQLSupport.AppendINStatement(command, "$ListDoctorID$", Parameter.ListDoctorID);
List <L5AC_GDSfDID_1214> results = new List <L5AC_GDSfDID_1214>();
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection, Transaction);
var reader = new CSV2Core_MySQL.Support.DBSQLReader(command.ExecuteReader());
try
{
reader.SetOrdinals(new string[] { "PotentialDiagnosis_Name_DictID", "IfPerfomed_DateOfAction", "Office_Name_DictID", "HEC_DIA_PotentialDiagnosisID", "CMN_STR_OfficeID", "HEC_Doctor_RefID", "HEC_ACT_PerformedActionID", "IsDiagnosisNegated", "DoctorTitle", "DoctorFirstName", "DoctorLastName", "ICD10_Code" });
while (reader.Read())
{
L5AC_GDSfDID_1214 resultItem = new L5AC_GDSfDID_1214();
//0:Parameter PotentialDiagnosis_Name of type Dict
resultItem.PotentialDiagnosis_Name = reader.GetDictionary(0);
resultItem.PotentialDiagnosis_Name.SourceTable = "hec_dia_potentialdiagnoses";
loader.Append(resultItem.PotentialDiagnosis_Name);
//1:Parameter IfPerfomed_DateOfAction of type DateTime
resultItem.IfPerfomed_DateOfAction = reader.GetDate(1);
//2:Parameter Office_Name of type Dict
resultItem.Office_Name = reader.GetDictionary(2);
resultItem.Office_Name.SourceTable = "cmn_str_offices";
loader.Append(resultItem.Office_Name);
//3:Parameter HEC_DIA_PotentialDiagnosisID of type Guid
resultItem.HEC_DIA_PotentialDiagnosisID = reader.GetGuid(3);
//4:Parameter CMN_STR_OfficeID of type Guid
resultItem.CMN_STR_OfficeID = reader.GetGuid(4);
//5:Parameter HEC_Doctor_RefID of type Guid
resultItem.HEC_Doctor_RefID = reader.GetGuid(5);
//6:Parameter HEC_ACT_PerformedActionID of type Guid
resultItem.HEC_ACT_PerformedActionID = reader.GetGuid(6);
//7:Parameter IsDiagnosisNegated of type bool
resultItem.IsDiagnosisNegated = reader.GetBoolean(7);
//8:Parameter DoctorTitle of type String
resultItem.DoctorTitle = reader.GetString(8);
//9:Parameter DoctorFirstName of type String
resultItem.DoctorFirstName = reader.GetString(9);
//10:Parameter DoctorLastName of type String
resultItem.DoctorLastName = reader.GetString(10);
//11:Parameter ICD10_Code of type String
resultItem.ICD10_Code = reader.GetString(11);
results.Add(resultItem);
}
}
catch (Exception ex)
{
reader.Close();
throw new Exception("Exception occured durng data retrieval in method cls_Get_DiagnosesStatistics_for_DoctorID", ex);
}
reader.Close();
//Load all the dictionaries from the datatables
loader.Load();
returnStatus.Result = results.ToArray();
return(returnStatus);
}
/// <summary>
/// Throws an <see cref="InvalidOperationException"/> if this method is called
/// because the user is responsible for closing <see cref="DbConnection"/>s.
/// </summary>
/// <param name="conn">The <see cref="DbConnection"/> to clean up.</param>
/// <exception cref="InvalidOperationException">
/// Thrown when this method is called. User is responsible for closing
/// <see cref="DbConnection"/>s.
/// </exception>
public override void CloseConnection(DbConnection conn)
{
throw new InvalidOperationException("The User is responsible for closing ADO.NET connection - not NHibernate.");
}
public DataTable ExecuteDataTable(DbCommand command, DbConnection connection)
{
return ExecuteDataTable(command, 0, 0, connection);
}
/// <summary> /// You have to provider a tracking table builder implementation for your current database /// </summary> public abstract IDbBuilderTrackingTableHelper CreateTrackingTableBuilder(DbConnection connection, DbTransaction transaction = null);
/// <summary>
/// Perform collection task specific processing.
/// </summary>
///
/// <param name="taskId">Database assigned task Id.</param>
/// <param name="cleId">Database Id of owning Collection Engine.</param>
/// <param name="elementId">Database Id of element being collected.</param>
/// <param name="databaseTimestamp">Database relatvie task dispatch timestamp.</param>
/// <param name="localTimestamp">Local task dispatch timestamp.</param>
/// <param name="attributes">Map of attribute names to Id for attributes being collected.</param>
/// <param name="scriptParameters">Collection script specific parameters (name/value pairs).</param>
/// <param name="connection">Connection script results (null if this script does not
/// require a remote host connection).</param>
/// <param name="tftpDispatcher">Dispatcher for TFTP transfer requests.</param>
///
/// <returns>Collection results.</returns>
///
public CollectionScriptResults ExecuteTask(
long taskId, long cleId, long elementId, long databaseTimestamp, long localTimestamp,
IDictionary <string, string> attributes, IDictionary <string, string> scriptParameters,
IDictionary <string, object> connection, string tftpPath, string tftpPath_login,
string tftpPath_password, ITftpDispatcher tftpDispatcher)
{
m_taskId = taskId.ToString();
m_cleId = cleId;
m_elementId = elementId;
m_databaseTimestamp = databaseTimestamp;
m_localTimestamp = localTimestamp;
m_attributes = attributes;
m_scriptParameters = scriptParameters;
m_executionTimer = Stopwatch.StartNew();
ResultCodes resultCode = ResultCodes.RC_BAD_COLLECTION_SCRIPT;
DbConnection dbConnection = null;
CollectionScriptResults result = null;
Lib.Logger.TraceEvent(TraceEventType.Start,
0,
"Task Id {0}: Collection script WDMOnMSSQLDeviceDetailsStaticScript.",
m_taskId);
try {
if (connection == null)
{
resultCode = ResultCodes.RC_NULL_CONNECTION_OBJECT;
}
else
{
//Check connection attributes
if (connection.ContainsKey("dbConnection"))
{
dbConnection = connection[@"dbConnection"] as DbConnection;
}
else
{
resultCode = ResultCodes.RC_SCRIPT_PARAMETER_MISSING;
Lib.Logger.TraceEvent(TraceEventType.Error,
0,
"Task Id {0}: Missing dbConnection script parameter.",
m_taskId);
}
}
if (dbConnection == null)
{
resultCode = ResultCodes.RC_NULL_CONNECTION_OBJECT;
}
else if (dbConnection.State != ConnectionState.Open)
{
resultCode = ResultCodes.RC_HOST_CONNECT_FAILED;
}
else
{
using (dbConnection) {
resultCode = GetDeviceDetails(dbConnection);
}
}
// Return RC_SUCCESS even if the query failed, as the database
// may be inaccessible
resultCode = ResultCodes.RC_SUCCESS;
StringBuilder dataRow = new StringBuilder();
dataRow.Append(elementId)
.Append(',')
.Append(m_attributes[@"deviceDetails"])
.Append(',')
.Append(m_scriptParameters[@"CollectorId"])
.Append(',')
.Append(m_taskId)
.Append(',')
.Append(m_databaseTimestamp + m_executionTimer.ElapsedMilliseconds)
.Append(',')
.Append(@"deviceDetails")
.Append(',')
.Append(BdnaDelimiters.BEGIN_TAG)
.Append(m_deviceDetails.ToString())
.Append(BdnaDelimiters.END_TAG);
result = new CollectionScriptResults(resultCode, 0, null, null, null, false, dataRow.ToString());
} catch (Exception e) {
Lib.Logger.TraceEvent(TraceEventType.Error,
0,
"Task Id {0}: Unhandled exception in WDMOnMSSQLDeviceDetailsStaticScript.\n{1}",
m_taskId,
e.ToString());
result = new CollectionScriptResults
(ResultCodes.RC_PROCESS_EXEC_FAILED, 0, null, null, null, false, null);
}
Debug.Assert(null != result);
Lib.Logger.TraceEvent(TraceEventType.Stop,
0,
"Task Id {0}: Connection script WDMOnMSSQLDeviceDetailsStaticScript. Elapsed time {1}. Result code {2}.",
m_taskId,
m_executionTimer.Elapsed.ToString(),
result.ResultCode.ToString());
return(result);
}
/// <summary>
/// Generate the creating script string (admin only)
/// </summary>
public string ScriptTable(DbConnection connection, DbTransaction transaction = null)
{
string str = null;
var alreadyOpened = connection.State != ConnectionState.Closed;
bool needToCreateTrackingTable = false;
try
{
if (!alreadyOpened)
{
connection.Open();
}
StringBuilder stringBuilder = new StringBuilder();
var tableBuilder = CreateTableBuilder(connection, transaction);
// Check if we have mutables columns
var hasMutableColumns = TableDescription.MutableColumnsAndNotAutoInc.Any();
// Check if we need to create the tables
if (tableBuilder.NeedToCreateTable())
{
if (tableBuilder.NeedToCreateSchema())
{
stringBuilder.Append(tableBuilder.CreateSchemaScriptText());
}
stringBuilder.Append(tableBuilder.CreateTableScriptText());
stringBuilder.Append(tableBuilder.CreatePrimaryKeyScriptText());
}
var trackingTableBuilder = CreateTrackingTableBuilder(connection, transaction);
trackingTableBuilder.Filters = this.FilterColumns.GetColumnFilters();
if (trackingTableBuilder.NeedToCreateTrackingTable())
{
stringBuilder.Append(trackingTableBuilder.CreateTableScriptText());
stringBuilder.Append(trackingTableBuilder.CreatePkScriptText());
stringBuilder.Append(trackingTableBuilder.CreateIndexScriptText());
needToCreateTrackingTable = true;
}
var triggerBuilder = CreateTriggerBuilder(connection, transaction);
triggerBuilder.Filters = this.FilterColumns.GetColumnFilters();
if (triggerBuilder.NeedToCreateTrigger(DbTriggerType.Insert))
{
stringBuilder.Append(triggerBuilder.CreateInsertTriggerScriptText());
}
if (triggerBuilder.NeedToCreateTrigger(DbTriggerType.Update))
{
stringBuilder.Append(triggerBuilder.CreateUpdateTriggerScriptText());
}
if (triggerBuilder.NeedToCreateTrigger(DbTriggerType.Delete))
{
stringBuilder.Append(triggerBuilder.CreateDeleteTriggerScriptText());
}
var procBuilder = CreateProcBuilder(connection, transaction);
if (procBuilder != null)
{
procBuilder.Filters = this.FilterColumns;
if (procBuilder.NeedToCreateProcedure(DbCommandType.SelectChanges))
{
stringBuilder.Append(procBuilder.CreateSelectIncrementalChangesScriptText());
}
if (procBuilder.NeedToCreateProcedure(DbCommandType.SelectRow))
{
stringBuilder.Append(procBuilder.CreateSelectRowScriptText());
}
if (procBuilder.NeedToCreateProcedure(DbCommandType.InsertRow))
{
stringBuilder.Append(procBuilder.CreateInsertScriptText());
}
if (hasMutableColumns && procBuilder.NeedToCreateProcedure(DbCommandType.UpdateRow))
{
stringBuilder.Append(procBuilder.CreateUpdateScriptText());
}
if (procBuilder.NeedToCreateProcedure(DbCommandType.DeleteRow))
{
stringBuilder.Append(procBuilder.CreateDeleteScriptText());
}
if (procBuilder.NeedToCreateProcedure(DbCommandType.InsertMetadata))
{
stringBuilder.Append(procBuilder.CreateInsertMetadataScriptText());
}
if (procBuilder.NeedToCreateProcedure(DbCommandType.UpdateMetadata))
{
stringBuilder.Append(procBuilder.CreateUpdateMetadataScriptText());
}
if (procBuilder.NeedToCreateProcedure(DbCommandType.DeleteMetadata))
{
stringBuilder.Append(procBuilder.CreateDeleteMetadataScriptText());
}
if (procBuilder.NeedToCreateProcedure(DbCommandType.Reset))
{
stringBuilder.Append(procBuilder.CreateResetScriptText());
}
if (this.UseBulkProcedures && procBuilder.NeedToCreateType(DbCommandType.BulkTableType))
{
stringBuilder.Append(procBuilder.CreateTVPTypeScriptText());
stringBuilder.Append(procBuilder.CreateBulkInsertScriptText());
if (hasMutableColumns)
{
stringBuilder.Append(procBuilder.CreateBulkUpdateScriptText());
}
stringBuilder.Append(procBuilder.CreateBulkDeleteScriptText());
}
if (needToCreateTrackingTable)
{
stringBuilder.Append(trackingTableBuilder.CreatePopulateFromBaseTableScriptText());
}
}
str = stringBuilder.ToString();
}
finally
{
if (!alreadyOpened)
{
connection.Close();
}
}
return(str);
}
/// <summary> /// Gets the table Sync Adapter in charge of executing all command during sync /// </summary> public abstract DbSyncAdapter CreateSyncAdapter(DbConnection connection, DbTransaction transaction = null);
public async Task OpenAsync()
{
connection = new SqlConnection(registry.ConnectionString);
await connection.OpenWithRetryAsync();
}
public DataTable GetSchema(string collection, string[] restrictionValues)
{
InitDB();
return(DbConnection.GetSchema(collection, restrictionValues));
}
//This constructor is used in tests
public SpartanDbContext(DbConnection connection)
: base(connection, true)
{
}
public GetEnquireModel GetEnquire(string chfid)
{
GetEnquireModel response = new GetEnquireModel();
List <DetailModel> details = new List <DetailModel>();
try
{
using (var imisContext = new ImisDB())
{
var chfidParameter = new SqlParameter("@CHFID", chfid)
{
SqlDbType = SqlDbType.VarChar, Size = 12
};
var sql = "exec uspAPIGetCoverage @CHFID";
DbConnection connection = imisContext.Database.GetDbConnection();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(new[] { chfidParameter });
if (connection.State.Equals(ConnectionState.Closed))
{
connection.Open();
}
using (var reader = cmd.ExecuteReader())
{
do
{
bool firstValue = true;
while (reader.Read())
{
if (firstValue)
{
response.InsureeName = String.Join(' ', reader["OtherNames"].ToString()) + ' ' + reader["LastName"].ToString();
response.CHFID = reader["CHFID"].ToString();
response.PhotoPath = reader["PhotoPath"].ToString();
response.DOB = reader["DOB"].ToString();
response.Gender = reader["Gender"].ToString();
firstValue = false;
}
details.Add(new DetailModel
{
AntenatalAmountLeft = reader["AntenatalAmountLeft"].ToString().ToNullableDecimal(),
ConsultationAmountLeft = reader["ConsultationAmountLeft"].ToString().ToNullableDecimal(),
DeliveryAmountLeft = reader["DeliveryAmountLeft"].ToString().ToNullableDecimal(),
HospitalizationAmountLeft = reader["HospitalizationAmountLeft"].ToString().ToNullableDecimal(),
SurgeryAmountLeft = reader["SurgeryAmountLeft"].ToString().ToNullableDecimal(),
TotalAdmissionsLeft = reader["TotalAdmissionsLeft"].ToString().ToNullableDecimal(),
TotalAntenatalLeft = reader["TotalAntenatalLeft"].ToString().ToNullableDecimal(),
TotalConsultationsLeft = reader["TotalConsultationsLeft"].ToString().ToNullableDecimal(),
TotalDelivieriesLeft = reader["TotalDelivieriesLeft"].ToString().ToNullableDecimal(),
TotalSurgeriesLeft = reader["TotalSurgeriesLeft"].ToString().ToNullableDecimal(),
TotalVisitsLeft = reader["TotalVisitsLeft"].ToString().ToNullableDecimal(),
PolicyValue = reader["PolicyValue"].ToString().ToNullableDecimal(),
EffectiveDate = reader["EffectiveDate"].ToString(),
ProductCode = reader["ProductCode"].ToString(),
ProductName = reader["ProductName"].ToString(),
ExpiryDate = reader["ExpiryDate"].ToString(),
Status = reader["Status"].ToString(),
DedType = reader["DedType"].ToString().ToNullableFloat(),
Ded1 = reader["Ded1"].ToString().ToNullableDecimal(),
Ded2 = reader["Ded2"].ToString().ToNullableDecimal(),
Ceiling1 = reader["Ceiling1"].ToString().ToNullableDecimal(),
Ceiling2 = reader["Ceiling2"].ToString().ToNullableDecimal(),
});
}
} while (reader.NextResult());
}
}
}
response.Details = details;
return(response);
}
catch (SqlException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
/// <summary> /// You have to provide a trigger builder implementation for your current database /// </summary> public abstract IDbBuilderTriggerHelper CreateTriggerBuilder(DbConnection connection, DbTransaction transaction = null);
private DbDataReader GetDataReader(DbConnection conn, string strSQL)
{
DbCommand command = GetCommand(conn, strSQL);
return(command.ExecuteReader());
}
/// <summary> /// You have to provide a proc builder implementation for your current database /// </summary> public abstract IDbBuilderProcedureHelper CreateProcBuilder(DbConnection connection, DbTransaction transaction = null);
private ResultCodes GetDeviceDetails(DbConnection dbConnection)
{
ResultCodes resultCode = ResultCodes.RC_PROCESSING_EXCEPTION;
Stopwatch sw = new Stopwatch();
int recordCount = 0;
try {
Lib.Logger.TraceEvent(TraceEventType.Verbose,
0,
"Task Id {0}: Executing device details query.",
m_taskId);
sw.Start();
m_databaseName = m_scriptParameters["databaseName"];
Lib.Logger.TraceEvent(TraceEventType.Information,
0,
"Task Id {0}: Connecting to database {1}.",
m_taskId,
m_databaseName);
dbConnection.ChangeDatabase(m_databaseName);
DbCommand cmd = dbConnection.CreateCommand();
cmd.CommandText =
@"select client.ClientID, client.Name, network.IP, network.MAC, " +
@"platform.Description as Platform, os.Description as OS, " +
@"client.Image, hardware.CPU, hardware.CPUSpeed, " +
@"hardware.RAM, storage.TotalSpace, hardware.Serial, " +
@"hardware.BIOS, hardware.Manufactured " +
@"from Client client, ClientHardware hardware, " +
@"ClientNetwork network, ClientStorage storage, " +
@"Platform platform, OSType os " +
@"where client.ClientID = hardware.ClientID " +
@"and client.ClientID = network.ClientID " +
@"and client.ClientID = storage.ClientID " +
@"and hardware.PlatformID = platform.PlatformID " +
@"and client.OSTypeID = os.OSTypeID ";
cmd.CommandType = CommandType.Text;
DbDataReader reader = cmd.ExecuteReader();
Debug.Assert(null != reader);
using (reader) {
for (recordCount = 0; reader.Read(); ++recordCount)
{
if (m_deviceDetails.Length > 0)
{
m_deviceDetails.Append(BdnaDelimiters.DELIMITER_TAG);
}
m_deviceDetails.Append("ClientID=").Append(reader[@"ClientID"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG);
m_deviceDetails.Append("Name=").Append(reader[@"Name"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG);
m_deviceDetails.Append("IPAddress=").Append(reader[@"IP"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG);
m_deviceDetails.Append("MACAddress=").Append(reader[@"MAC"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG);
m_deviceDetails.Append("Platform=").Append(reader[@"Platform"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG);
m_deviceDetails.Append("OS=").Append(reader[@"OS"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG);
m_deviceDetails.Append("Image=").Append(reader[@"Image"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG);
m_deviceDetails.Append("CPU=").Append(reader[@"CPU"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG);
m_deviceDetails.Append("CPUSpeed=").Append(reader[@"CPUSpeed"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG);
m_deviceDetails.Append("RAM=").Append(reader[@"RAM"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG);
m_deviceDetails.Append("MediaSize=").Append(reader[@"TotalSpace"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG);
m_deviceDetails.Append("SerialNumber=").Append(reader[@"Serial"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG);
m_deviceDetails.Append("BIOS=").Append(reader[@"BIOS"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG);
m_deviceDetails.Append("ManufacturedOn=").Append(reader[@"Manufactured"].ToString());
}
Lib.Logger.TraceEvent(TraceEventType.Verbose,
0,
"Task Id {0}: Processing device details complete.",
m_taskId);
}
resultCode = ResultCodes.RC_SUCCESS;
} catch (DbException dbEx) {
Lib.Logger.TraceEvent(TraceEventType.Error,
0,
"Task Id {0}: Device detail query failed. Elapsed time {1}.\n{2}",
m_taskId,
sw.Elapsed.ToString(),
FormatDbException(dbEx));
} catch (Exception ex) {
Lib.Logger.TraceEvent(TraceEventType.Error,
0,
"Task Id {0}: Device detail query failed. Elapsed time {1}.\nRecords processed {2}\n{3}",
m_taskId,
sw.Elapsed.ToString(),
recordCount.ToString(),
ex.ToString());
}
return(resultCode);
}
public void DropProcedures(DbConnection connection, DbTransaction transaction = null)
{
var alreadyOpened = connection.State != ConnectionState.Closed;
// Check if we have mutables columns
var hasMutableColumns = TableDescription.MutableColumnsAndNotAutoInc.Any();
try
{
if (!alreadyOpened)
{
connection.Open();
}
var procBuilder = CreateProcBuilder(connection, transaction);
// Could be null
if (procBuilder == null)
{
return;
}
procBuilder.Filters = this.FilterColumns;
if (!procBuilder.NeedToCreateProcedure(DbCommandType.SelectChanges))
{
procBuilder.DropSelectIncrementalChanges();
}
if (!procBuilder.NeedToCreateProcedure(DbCommandType.SelectRow))
{
procBuilder.DropSelectRow();
}
if (!procBuilder.NeedToCreateProcedure(DbCommandType.InsertRow))
{
procBuilder.DropInsert();
}
if (hasMutableColumns && !procBuilder.NeedToCreateProcedure(DbCommandType.UpdateRow))
{
procBuilder.DropUpdate();
}
if (!procBuilder.NeedToCreateProcedure(DbCommandType.DeleteRow))
{
procBuilder.DropDelete();
}
if (!procBuilder.NeedToCreateProcedure(DbCommandType.InsertMetadata))
{
procBuilder.DropInsertMetadata();
}
if (!procBuilder.NeedToCreateProcedure(DbCommandType.UpdateMetadata))
{
procBuilder.DropUpdateMetadata();
}
if (!procBuilder.NeedToCreateProcedure(DbCommandType.DeleteMetadata))
{
procBuilder.DropDeleteMetadata();
}
if (!procBuilder.NeedToCreateProcedure(DbCommandType.Reset))
{
procBuilder.DropReset();
}
if (this.UseBulkProcedures && !procBuilder.NeedToCreateType(DbCommandType.BulkTableType))
{
procBuilder.DropBulkInsert();
if (hasMutableColumns)
{
procBuilder.DropBulkUpdate();
}
procBuilder.DropBulkDelete();
procBuilder.DropTVPType();
}
}
finally
{
if (!alreadyOpened)
{
connection.Close();
}
}
}
public DbConnection CreateConnection()
{
DbConnection connection = Factory.CreateConnection();
connection.ConnectionString = ConnectionString;
return connection;
}
public void CreateStoredProcedures(DbConnection connection, DbTransaction transaction = null)
{
if (!TableDescription.PrimaryKey.HasValue)
{
throw new InvalidOperationException($"Create stored procedures: Table {TableDescription.TableName} must have at least one dmColumn as Primary key");
}
// Check if we have mutables columns
var hasMutableColumns = TableDescription.MutableColumnsAndNotAutoInc.Any();
var alreadyOpened = connection.State != ConnectionState.Closed;
try
{
// could be null
var procBuilder = CreateProcBuilder(connection, transaction);
if (procBuilder == null)
{
return;
}
procBuilder.Filters = this.FilterColumns;
if (procBuilder.NeedToCreateProcedure(DbCommandType.SelectChanges))
{
procBuilder.CreateSelectIncrementalChanges();
}
if (procBuilder.NeedToCreateProcedure(DbCommandType.SelectRow))
{
procBuilder.CreateSelectRow();
}
if (procBuilder.NeedToCreateProcedure(DbCommandType.InsertRow))
{
procBuilder.CreateInsert();
}
if (hasMutableColumns && procBuilder.NeedToCreateProcedure(DbCommandType.UpdateRow))
{
procBuilder.CreateUpdate();
}
if (procBuilder.NeedToCreateProcedure(DbCommandType.DeleteRow))
{
procBuilder.CreateDelete();
}
if (procBuilder.NeedToCreateProcedure(DbCommandType.InsertMetadata))
{
procBuilder.CreateInsertMetadata();
}
if (procBuilder.NeedToCreateProcedure(DbCommandType.UpdateMetadata))
{
procBuilder.CreateUpdateMetadata();
}
if (procBuilder.NeedToCreateProcedure(DbCommandType.DeleteMetadata))
{
procBuilder.CreateDeleteMetadata();
}
if (procBuilder.NeedToCreateProcedure(DbCommandType.Reset))
{
procBuilder.CreateReset();
}
if (this.UseBulkProcedures && procBuilder.NeedToCreateType(DbCommandType.BulkTableType))
{
procBuilder.CreateTVPType();
procBuilder.CreateBulkInsert();
if (hasMutableColumns)
{
procBuilder.CreateBulkUpdate();
}
procBuilder.CreateBulkDelete();
}
}
finally
{
if (!alreadyOpened)
{
connection.Close();
}
}
}
public DataTable ExecuteDataTable(DbCommand command, int startRecord, int maxRecords, DbConnection connection)
{
OnExecuteCommand(command);
command.Connection = connection;
DbDataAdapter adapter = Factory.CreateDataAdapter();
adapter.SelectCommand = command;
DataTable dt = new DataTable();
if (startRecord > 0 || maxRecords > 0)
adapter.Fill(startRecord, maxRecords, dt);
else
adapter.Fill(dt);
return dt;
}
public override void InitPipeline(ISpider spider)
{
if (string.IsNullOrEmpty(ConnectString))
{
if (UpdateConnectString == null)
{
throw new SpiderException("Can't find ConnectString or IUpdateConnectString.");
}
else
{
for (int i = 0; i < 5; ++i)
{
try
{
ConnectString = UpdateConnectString.GetNew();
break;
}
catch (Exception e)
{
spider.Log("Update ConnectString failed.", LogLevel.Error, e);
Thread.Sleep(1000);
}
}
if (string.IsNullOrEmpty(ConnectString))
{
throw new SpiderException("Can't updadate ConnectString via IUpdateConnectString.");
}
}
}
base.InitPipeline(spider);
foreach (var metadata in DbMetadatas.Values)
{
if (!metadata.IsInsertModel)
{
continue;
}
NetworkCenter.Current.Execute("db-init", () =>
{
using (DbConnection conn = CreateConnection())
{
var command = conn.CreateCommand();
command.CommandText = GetIfSchemaExistsSql(metadata, conn.ServerVersion);
if (Convert.ToInt16(command.ExecuteScalar()) == 0)
{
command.CommandText = GetCreateSchemaSql(metadata, conn.ServerVersion);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
command.CommandText = GetCreateTableSql(metadata);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
});
}
}
public int ExecuteNonQuery(DbCommand command, DbConnection connection)
{
OnExecuteCommand(command);
command.Connection = connection;
return command.ExecuteNonQuery();
}
public DerpyDbHistoryContext(DbConnection dbConnection, string defaultSchema)
: base(dbConnection, defaultSchema)
{
}
///<summary>
/// Invokes the method for the given Connection, and Transaction, leaving them open/not commited if no exceptions occured
///<summary>
public static FR_L5AC_GDSfDID_1214_Array Invoke(DbConnection Connection, DbTransaction Transaction, P_L5AC_GDSfDID_1214 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
return(Invoke(Connection, Transaction, null, Parameter, securityTicket));
}
protected override DataTable CheckConstraints(string tableName, DbConnection connection)
{
string sqlCommand = GetCheckSql();
return(CommandForTable(tableName, connection, CheckConstraintsCollectionName, sqlCommand));
}
public int AddNS(NotaSalida entity)
{
int id;
int idKardex;
int idND;
int idStock;
using (DbConnection conexion = _database.CreateConnection())
{
conexion.Open();
using (var transaction = conexion.BeginTransaction(IsolationLevel.Serializable))
{
try
{
using (var comando = _database.GetStoredProcCommand(string.Format("{0}{1}", ConectionStringRepository.EsquemaName, "SGE_NOTA_SALIDA_INSERT")))
{
_database.AddInParameter(comando, "@nsalc_numero_nota_salida", DbType.String, entity.nsalc_numero_nota_salida);
_database.AddInParameter(comando, "@almac_icod_almacen", DbType.Int32, entity.almac_icod_almacen);
_database.AddInParameter(comando, "@nsalc_iid_motivo", DbType.Int32, entity.nsalc_iid_motivo);
_database.AddInParameter(comando, "@nsalc_fecha_nota_salida", DbType.String, entity.nsalc_fecha_nota_salida_);
_database.AddInParameter(comando, "@tdocc_icod_tipo_doc", DbType.Int32, entity.tdocc_icod_tipo_doc);
_database.AddInParameter(comando, "@nsalc_referencia", DbType.String, entity.nsalc_referencia);
_database.AddInParameter(comando, "@nsalc_observaciones", DbType.String, entity.nsalc_observaciones);
_database.AddInParameter(comando, "@nsalc_usuario_crea", DbType.String, entity.UsuarioCreacion);
_database.AddInParameter(comando, "@nsalc_pc_crea", DbType.String, WindowsIdentity.GetCurrent().Name);
_database.AddInParameter(comando, "@nsalc_ilag_estado", DbType.Int32, entity.Estado);
_database.AddOutParameter(comando, "@Response", DbType.Int32, 11);
_database.ExecuteNonQuery(comando, transaction);
id = Convert.ToInt32(_database.GetParameterValue(comando, "@Response"));
if (id == 0)
{
throw new Exception("Error al ingresar Nota Ingreso");
}
}
foreach (var itemdetalle in entity.listaDetalleNS)
{
using (var comandoKardex = _database.GetStoredProcCommand(string.Format("{0}{1}", ConectionStringRepository.EsquemaName, "SGE_KARDEX_INSERT")))
{
_database.AddInParameter(comandoKardex, "@kardc_fecha_movimiento", DbType.String, entity.nsalc_fecha_nota_salida_);
_database.AddInParameter(comandoKardex, "@ningc_icod_nota_ingreso", DbType.Int32, id);
_database.AddInParameter(comandoKardex, "@almac_icod_almacen", DbType.Int32, entity.almac_icod_almacen);
_database.AddInParameter(comandoKardex, "@prdc_icod_producto", DbType.Int32, itemdetalle.prdc_icod_producto);
_database.AddInParameter(comandoKardex, "@kardc_icantidad_prod", DbType.Decimal, itemdetalle.nsald_cantidad);
_database.AddInParameter(comandoKardex, "@tdocc_icod_tipo_doc", DbType.Int32, entity.tdocc_icod_tipo_doc);
_database.AddInParameter(comandoKardex, "@kardc_numero_doc", DbType.String, entity.nsalc_numero_nota_salida);
_database.AddInParameter(comandoKardex, "@kardc_tipo_movimiento", DbType.Int32, 2);
_database.AddInParameter(comandoKardex, "@kardc_monto_unitario_compra", DbType.Decimal, 0);
_database.AddInParameter(comandoKardex, "@kardc_iid_motivo", DbType.Int32, entity.nsalc_iid_motivo);
_database.AddInParameter(comandoKardex, "@kardc_beneficiario", DbType.String, entity.nsalc_referencia);
_database.AddInParameter(comandoKardex, "@kardc_observaciones", DbType.String, entity.nsalc_observaciones);
_database.AddInParameter(comandoKardex, "@kardc_vusuario_crea", DbType.String, entity.UsuarioCreacion);
_database.AddInParameter(comandoKardex, "@kardc_vpc_crea", DbType.String, WindowsIdentity.GetCurrent().Name);
_database.AddInParameter(comandoKardex, "@kardc_ilag_estado", DbType.Int32, entity.Estado);
_database.AddOutParameter(comandoKardex, "@Response", DbType.Int32, 11);
_database.ExecuteNonQuery(comandoKardex, transaction);
idKardex = Convert.ToInt32(_database.GetParameterValue(comandoKardex, "@Response"));
if (idKardex == 0)
{
throw new Exception("Error al ingresar kardex");
}
}
using (var comando = _database.GetStoredProcCommand(string.Format("{0}{1}", ConectionStringRepository.EsquemaName, "SGE_NOTA_SALIDA_DETALLE_INSERT")))
{
_database.AddInParameter(comando, "@nsalc_icod_nota_salida", DbType.Int32, id);
_database.AddInParameter(comando, "@nsald_nro_item", DbType.String, itemdetalle.nsald_nro_item);
_database.AddInParameter(comando, "@prdc_icod_producto", DbType.Int32, itemdetalle.prdc_icod_producto);
_database.AddInParameter(comando, "@nsald_cantidad", DbType.Decimal, itemdetalle.nsald_cantidad);
_database.AddInParameter(comando, "@kardc_icod_correlativo", DbType.Int32, idKardex);
_database.AddInParameter(comando, "@nsald_usuario_crea", DbType.String, entity.UsuarioCreacion);
_database.AddInParameter(comando, "@nsald_pc_crea", DbType.String, WindowsIdentity.GetCurrent().Name);
_database.AddInParameter(comando, "@nsald_ilag_estado", DbType.Int32, entity.Estado);
_database.AddOutParameter(comando, "@Response", DbType.Int32, 11);
_database.ExecuteNonQuery(comando, transaction);
idND = Convert.ToInt32(_database.GetParameterValue(comando, "@Response"));
if (idND == 0)
{
throw new Exception("Error al ingresar nota de ingreso detalle");
}
}
using (var ComandoStock = _database.GetStoredProcCommand(string.Format("{0}{1}", ConectionStringRepository.EsquemaName, "SGE_STOCK_UPDATE")))
{
_database.AddInParameter(ComandoStock, "@almac_icod_almacen", DbType.Int32, entity.almac_icod_almacen);
_database.AddInParameter(ComandoStock, "@prdc_icod_producto", DbType.Int32, itemdetalle.prdc_icod_producto);
_database.AddInParameter(ComandoStock, "@stocc_stock_producto", DbType.Decimal, itemdetalle.nsald_cantidad);
_database.AddInParameter(ComandoStock, "@stocc_ilag_estado", DbType.Int32, entity.Estado);
_database.AddInParameter(ComandoStock, "@tipo_movimiento", DbType.Int32, 2);
_database.AddOutParameter(ComandoStock, "@Response", DbType.Int32, 11);
_database.ExecuteNonQuery(ComandoStock, transaction);
idStock = Convert.ToInt32(_database.GetParameterValue(ComandoStock, "@Response"));
if (idStock == 0)
{
throw new Exception("Error al ingresar stock");
}
}
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw new Exception(ex.Message);
}
}
}
return(id);
}
protected override DataTable StoredProcedureArguments(string storedProcedureName, DbConnection connection)
{
var argReader = new PostgreSqlArgumentReader(Factory, Owner);
var dt = argReader.StoredProcedureArguments(storedProcedureName, connection);
dt.TableName = ProcedureParametersCollectionName;
return(dt);
}
public override IDataBaseSchema GetDataBaseSchema(DbConnection connection)
{
return(new MsSqlCeDataBaseSchema(connection));
}
///<summary>
/// Invokes the method for the given Connection, and Transaction, leaving them open/not commited if no exceptions occured
///<summary>
public static FR_Base Invoke(DbConnection Connection, DbTransaction Transaction, P_L5SQ_DU_1137 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null)
{
return(Invoke(Connection, Transaction, null, Parameter, securityTicket));
}
