protected void showtb_Click(object sender, EventArgs e)
{
conn = new SqlConnection(connstr);
cmd = new SqlCommand("select * from Registration", conn);
System.Data.DataTable dt = new System.Data.DataTable();
conn.Open();
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
dt.Load(rdr);
grdMemberView.DataSource = dt;
grdMemberView.DataBind();
}
else
{
dt.Load(rdr);
grdMemberView.DataSource = dt;
grdMemberView.EmptyDataText = "No members to show";
grdMemberView.DataBind();
}
conn.Close();
}
public System.Data.DataTable UserDataConnections(string name, int legajo, int tipo, int nroSucursal)
{
SqlConnection connection;
SqlCommand command;
string sql = "";
SqlDataReader dataReader;
System.Data.DataTable dt = new System.Data.DataTable();
sql = StringQuery(name, legajo, tipo, nroSucursal);
connection = new SqlConnection(connectionString);
try
{
connection.Open();
command = new SqlCommand(sql, connection);
dataReader = command.ExecuteReader();
/*while (dataReader.Read())
* {*/
dt.Load(dataReader);
//}
dataReader.Close();
command.Dispose();
connection.Close();
}
catch (Exception ex)
{
dt = null;
}
return(dt);
}
public void Init()
{
var connectionString = new SQLiteConnectionStringBuilder
{
DataSource = $"{SqlFileName}"
};
try
{
using (var con = new SQLiteConnection(connectionString.ToString()))
{
con.Open();
var cmd = con.CreateCommand();
cmd.CommandText = InitQuery;
var reader = cmd.ExecuteReader();
//table = reader.GetSchemaTable().DefaultView;
var datatable = new System.Data.DataTable();
datatable.Load(reader);
table = datatable.DefaultView;
con.Close();
}
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
}
}
} //OpenConnect(string connName)
/// <summary>
/// 执行查询返回DataTable
/// </summary>
/// <param name="sql">Sql语句</param>
/// <returns>成功返回DataTable,失败则返回 null</returns>
public static System.Data.DataTable ExecuteQueryToDataTable(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param = null)
{
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.IDataReader reader = ExecuteQuery(sql, Conn, param);
dt.Load(reader);
return(dt);
} //ExecuteQueryToDataTable(string sql)
/// <summary> Small helper method querying and returning a textual representation of the SQLite execution plan. </summary>
public static string GetSQLiteExecutionPlan(this System.Data.IDbCommand cmd)
{
using (var qpCmd = cmd.Connection.CreateCommand())
{
qpCmd.CommandText = "EXPLAIN QUERY PLAN " + cmd.CommandText;
qpCmd.Connection = cmd.Connection;
qpCmd.CommandType = cmd.CommandType;
qpCmd.Transaction = cmd.Transaction;
foreach (System.Data.IDataParameter p in cmd.Parameters)
{
qpCmd.AddParameter(p.Value, p.ParameterName);
}
System.Data.DataTable dt = new System.Data.DataTable();
using (var rd = qpCmd.ExecuteReader())
dt.Load(rd);
string plan =
String.Join("\t", dt.Columns.Cast <System.Data.DataColumn>().Select(c => c.ColumnName).ToArray()) + "\n"
+ string.Join("\n", dt.Rows.Cast <System.Data.DataRow>().Select(r => String.Join("\t", r.ItemArray.Select(o => o == null ? "" : o.ToString()).ToArray())).ToArray());
return(plan);
}
}
public ProductImage GetProductImage(string productCode)
{
var productimage = db.ExecuteSprocAccessor(DBRoutine.SELECTPRODUCTIMAGE,
MapBuilder <ProductImage>
.BuildAllProperties(),
productCode).FirstOrDefault();
byte[] photo;
if (productimage != null)
{
var searchcommand = db.GetStoredProcCommand(DBRoutine.SELECTPRODUCTIMAGE);
db.AddInParameter(searchcommand, "ProductCode", System.Data.DbType.String, productCode);
var reader = (System.Data.IDataReader)db.ExecuteReader(searchcommand);
System.Data.DataTable dt = new System.Data.DataTable();
dt.Load(reader);
photo = (byte[])dt.Rows[0]["ProductImg"];
productimage.ProductImg = photo;
}
return(productimage);
}
private static async System.Threading.Tasks.Task <System.Data.DataTable> fetchDataTable(NamedQuery qry, string ConnectionString)
{
// Create a connection, open it and create a command on the connection
try
{
System.Data.DataTable dt = new System.Data.DataTable(qry.TableName);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
await connection.OpenAsync().ConfigureAwait(false);
System.Diagnostics.Debug.WriteLine("Connection Opened ... " + qry.TableName);
using (SqlCommand command = new SqlCommand(qry.SQL, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
System.Diagnostics.Debug.WriteLine("Query Executed ... " + qry.TableName);
dt.Load(reader);
System.Diagnostics.Debug.WriteLine(string.Format("Record Count '{0}' ... {1}", dt.Rows.Count, qry.TableName));
return(dt);
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("Exception Raised ... " + qry.TableName);
System.Diagnostics.Debug.WriteLine(ex.Message);
return(new System.Data.DataTable(qry.TableName));
}
}
public Boolean Get_Job_Details(Int32 jobId)
{
Boolean isSuccessful = true;
ErrorMessage = string.Empty;
try
{
String strSQL = "SELECT * FROM JobDetails ";
strSQL = strSQL + "INNER JOIN WorkOrderDetails ON JobDetails.WorkOrderDetailId = WorkOrderDetails.WorkorderDetailId ";
strSQL = strSQL + "INNER JOIN Parts ON WorkOrderDetails.PartId = Parts.PartId ";
strSQL = strSQL + "WHERE JobDetails.Jobid = " + jobId.ToString();
System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection);
System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader();
if (rdrGet.HasRows == true)
{
myJobDetails.Clear();
myJobDetails.Load(rdrGet);
}
rdrGet.Close();
cmdGet.Dispose();
}
catch (Exception ex)
{
isSuccessful = false;
ErrorMessage = "Get Job Details - " + ex.Message;
}
return(isSuccessful);
}
public Boolean Get_Utilisation_Record(Int32 lineId, DateTime workDay)
{
Boolean isSuccessful = true;
ErrorMessage = string.Empty;
try
{
String strSQL = "SELECT * FROM ProductionLineUtilisation WHERE LineUtilisationLineId = " + lineId.ToString() + " AND LineUtilisationWorkDate = CONVERT(datetime,'" + workDay.ToShortDateString() + " 00:00:00', 103)";
System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection);
System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader();
if (rdrGet.HasRows == true)
{
UtilisationRecord.Clear();
UtilisationRecord.Load(rdrGet);
isSuccessful = Gather_Utilisation_Record();
}
else
{
isSuccessful = false;
ErrorMessage = "Get Production Line Utilisation Record - Not Found !";
}
rdrGet.Close();
cmdGet.Dispose();
}
catch (Exception ex)
{
isSuccessful = false;
ErrorMessage = "Get Production Line Utilisation Record - " + ex.Message;
}
return(isSuccessful);
}
private System.Data.DataTable GetData(int city, string lang)
{
try
{
SqlConnection myConnection = new SqlConnection("user id=Uib;" +
"password=Uib;server=localhost;" +
"Trusted_Connection=yes;" +
"database=Uib; " +
"connection timeout=30");
myConnection.Open();
var command = new SqlCommand("GET_HOTELS_BY_CITY", myConnection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@city", city);
command.Parameters.AddWithValue("@lang", lang);
SqlDataReader reader = command.ExecuteReader();
var dataTable = new System.Data.DataTable();
dataTable.Load(reader);
reader.Close();
myConnection.Close();
return dataTable;
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
return null;
}
public void SQLServerExecuteReader()
{
var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString);
var scriptBuilder = conn.GetScriptBuild();
var cliente = new Cliente()
{
Nome = "Miranda"
};
var createTableScript = scriptBuilder.GetCreateTableCommand <Cliente>();
conn.Execute(createTableScript);
var id = conn.InsertReturningId <Cliente>(cliente);
cliente.Id = id;
cliente.Nome = "Moisés Miranda";
conn.Insert <Cliente>(cliente);
var reader = scriptBuilder.ExecuteReader("select * from Cliente", conn);
var data = new System.Data.DataTable();
data.Load(reader);
conn.Execute("drop table [Cliente]");
Assert.AreEqual(2, data.Rows.Count);
}
public void PopulateEmployerGroupDDL()
{
dbProcedures db = new dbProcedures();
try
{
System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("SELECT DISTINCT EmployerGroup FROM tbl_Members ORDER BY EmployerGroup", new db().SqlConnection);
sqlCmd.CommandType = System.Data.CommandType.Text;
System.Data.DataTable loadReaderdata = new System.Data.DataTable();
loadReaderdata.Load(sqlCmd.ExecuteReader());
ddlEmployerGroup.DataSource = loadReaderdata;
ddlEmployerGroup.DataTextField = "EmployerGroup";
ddlEmployerGroup.DataValueField = "EmployerGroup";
ddlEmployerGroup.DataBind();
ddlEmployerGroup.Items.Insert(0, new ListItem("", ""));
ddlEmployerGroup.SelectedIndex = 0;
}
catch (Exception ex)
{
db.LogAction("", "Error", "Error loading Members UI page. " + ex.Message.ToString());
}
db.Close();
}
public static DataSet1 get_report()
{
DataSet1 ds = new DataSet1();
try
{
using (var db = new Models.jotunContext())
{
db.Database.Connection.Open();
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = "select * from view_barang order by terjual desc";
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
ds.Tables["view_barang"].Merge(dt);
}
}
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return ds;
}
public Boolean Get_Progress_Record(Int32 jobId)
{
Boolean isSuccessful = true;
System.Data.DataTable myProgressRecord = new System.Data.DataTable();
ErrorMessage = string.Empty;
try
{
String strSQL = "SELECT * FROM JobProgress WHERE ProgressJobId = " + jobId.ToString();
System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection);
System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader();
if (rdrGet.HasRows == true)
{
myProgressRecord.Load(rdrGet);
isSuccessful = Gather_Progress_Record(myProgressRecord);
}
else
{
isSuccessful = false;
}
rdrGet.Close();
cmdGet.Dispose();
}
catch (Exception ex)
{
isSuccessful = false;
ErrorMessage = "Get Progress Record - " + ex.Message;
}
return(isSuccessful);
}
public static DataSet1 get_report_per_jenis(string param)
{
DataSet1 result = new DataSet1();
try
{
string sql = string.Format("select * from barang where jenis in ({0})", "'" + param.Replace(", ", "','") + "'");
using (var db = new Models.sim_penjualanContext())
{
db.Database.Connection.Open();
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
result.Tables["barang"].Merge(dt);
}
}
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(result);
}
/// Get a data table from database.
public static System.Data.DataTable SelectDataTable(string sql, params SqlParameter[] arrayOfParameters)
{
System.Data.DataTable dtReturn = new System.Data.DataTable();
using (SqlConnection cn = new SqlConnection(ConnectionString))
{
//using (SqlDataAdapter da = new SqlDataAdapter(sql, cn))
//{
// //if (arrayOfParameters != null && arrayOfParameters.Length > 0)
// if ((arrayOfParameters?.Length ?? 0) > 0) da.SelectCommand.Parameters.AddRange(arrayOfParameters);
// cn.Open();
// da.Fill(dtReturn);
//}
using (SqlCommand com = new SqlCommand(sql, cn))
{
if ((arrayOfParameters?.Length ?? 0) > 0)
{
com.Parameters.AddRange(arrayOfParameters);
}
com.Connection.Open();
using (SqlDataReader rdr = com.ExecuteReader())
{
dtReturn.Load(rdr);
}
}
return(dtReturn);
}
}
// Is Customer
public bool isCustomer(long customerID)
{
// Validate Connection
if (this.connection == null)
{
return(false);
}
// Validate Customer ID
if (customerID < 0)
{
return(false);
}
// Build the SQL statement
string sql = "SELECT CustomerID FROM Customers WHERE CustomerID=" + customerID.ToString();
System.Console.WriteLine(sql);
// Create and execute the SQL command
MySqlCommand command = new MySqlCommand(sql, this.connection);
MySqlDataReader result = command.ExecuteReader();
// Find out how many rows there are
System.Data.DataTable dataTable = new System.Data.DataTable();
dataTable.Load(result);
result.Close();
if (dataTable.Rows.Count == 1)
{
// We found a customer with the ID provided
return(true);
}
// We did not find a customer with the ID provided
return(false);
}
public Boolean Job_Already_Completed(String jobNumber, System.Data.SqlClient.SqlTransaction trnEnvelope)
{
Boolean isCompleted = false;
System.Data.DataTable thisJob = new System.Data.DataTable();
ErrorMessage = string.Empty;
try
{
String strSQL = "SELECT * FROM Jobs WHERE JobNumber = '" + jobNumber + "'";
System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection, trnEnvelope);
System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader();
if (rdrGet.HasRows == true)
{
thisJob.Load(rdrGet);
if (thisJob.Rows[0]["JobStatus"].ToString() == "Completed")
{
isCompleted = true;
}
}
rdrGet.Close();
cmdGet.Dispose();
}
catch (Exception ex)
{
ErrorMessage = "Check Job Completed - " + ex.Message;
}
return(isCompleted);
}
}// mExecutacomandoSql
public System.Data.DataTable mRetornaDataTable(string strSql)
{
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand(strSql, conn);
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = strSql;
System.Data.OleDb.OleDbDataReader dr;
System.Data.DataTable dt = new System.Data.DataTable("dados");
if (mConectaDbOracle())
{
try
{
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dt.Load(dr);
dr.Close();
}
}
catch (Exception ex)
{
error_ = ex.Message;
log.Error("Erro no banco:->" + strSql + "\r\n" + ex.Message + "\r\n");
mDesconectaDbOracle();
}
finally
{
mDesconectaDbOracle();
}
}
return(dt);
} //mVerificaExistenciaRegistroSql
public IEnumerable <Tank> Create(int user_tank_num, string path)
{
if (user_tank_num == 1)
{
path += @"\279.csv";
//return new Tank("", 1, 1, 1);
}
else if (user_tank_num == 2)
{
path += @"\907.csv";
}
else
{
path += @"\Chief.csv";
}
var csvTable = new System.Data.DataTable();
using (var csvReader = new CsvReader(new StreamReader(System.IO.File.OpenRead(path)), true))
{
csvTable.Load(csvReader);
}
yield return(new Tank(csvTable.Rows[0][0].ToString(), Convert.ToInt32(csvTable.Rows[0][1]), Convert.ToInt32(csvTable.Rows[0][2]), Convert.ToInt32(csvTable.Rows[0][3])));
}
public Boolean Get_Line_Defaults()
{
Boolean isSuccessful = true;
System.Data.DataTable myDefaults = new System.Data.DataTable();
ErrorMessage = string.Empty;
try
{
String strSQL = "SELECT * FROM SystemDefaults";
System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection);
System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader();
if (rdrGet.HasRows == true)
{
myDefaults.Load(rdrGet);
DefaultStartTime = Convert.ToDateTime(myDefaults.Rows[0]["ProductionStartTime"].ToString()).TimeOfDay;
DefaultStopTime = Convert.ToDateTime(myDefaults.Rows[0]["ProductionFinishTime"].ToString()).TimeOfDay;
}
else
{
isSuccessful = false;
ErrorMessage = "Get Production Line Defaults - Not Found !";
}
rdrGet.Close();
cmdGet.Dispose();
}
catch (Exception ex)
{
isSuccessful = false;
ErrorMessage = "Get Production Line Defaults - " + ex.Message;
}
return(isSuccessful);
}
public String MultiCoat_PaintProduct(Int32 sequence, Int32 workOrderId)
{
String myPrimer = string.Empty;
ErrorMessage = string.Empty;
try
{
String strSQL = "SELECT WorkOrdersPrimer.*, PaintSystemSteps.ProcessSeq, SupplierPaintProducts.SupplierPaintProductCode, ColorName.Description as ColourName, SupplierProductGroups.SupplierProductGroupCode ";
strSQL = strSQL + "FROM WorkOrdersPrimer ";
strSQL = strSQL + "INNER JOIN PaintSystemSteps ON WorkOrdersPrimer.PaintSystemStepId = PaintSystemSteps.PaintSystemStepId ";
strSQL = strSQL + "INNER JOIN SupplierPaintProducts ON WorkOrdersPrimer.SupplierPaintProductId = SupplierPaintProducts.SupplierPaintProductId ";
strSQL = strSQL + "INNER JOIN ColorName ON SupplierPaintProducts.ColorNameId = ColorName.ColorNameId ";
strSQL = strSQL + "INNER JOIN SupplierProductGroups ON SupplierPaintProducts.SupplierProductGroupId = SupplierProductGroups.SupplierProductGroupId ";
strSQL = strSQL + "WHERE WorkOrdersPrimer.WorkOrderId = " + workOrderId.ToString() + " ";
strSQL = strSQL + "ORDER BY PaintSystemSteps.ProcessSeq";
System.Data.SqlClient.SqlCommand cmdGet = new System.Data.SqlClient.SqlCommand(strSQL, myVPSConnection);
System.Data.SqlClient.SqlDataReader rdrGet = cmdGet.ExecuteReader();
if (rdrGet.HasRows)
{
System.Data.DataTable myPrimers = new System.Data.DataTable();
myPrimers.Load(rdrGet);
if (sequence == 1)
{
myPrimer = myPrimers.Rows[0]["SupplierProductGroupCode"].ToString() + ",";
myPrimer = myPrimer + myPrimers.Rows[0]["SupplierPaintProductCode"].ToString() + ",";
myPrimer = myPrimer + myPrimers.Rows[0]["ColourName"].ToString();
}
else if (sequence == 2)
{
myPrimer = myPrimers.Rows[1]["SupplierProductGroupCode"].ToString() + ",";
myPrimer = myPrimer + myPrimers.Rows[1]["SupplierPaintProductCode"].ToString() + ",";
myPrimer = myPrimer + myPrimers.Rows[1]["ColourName"].ToString();
}
else if (sequence == 3)
{
myPrimer = myPrimers.Rows[2]["SupplierProductGroupCode"].ToString() + ",";
myPrimer = myPrimer + myPrimers.Rows[2]["SupplierPaintProductCode"].ToString() + ",";
myPrimer = myPrimer + myPrimers.Rows[2]["ColourName"].ToString();
}
else if (sequence == 4)
{
myPrimer = myPrimers.Rows[3]["SupplierProductGroupCode"].ToString() + ",";
myPrimer = myPrimer + myPrimers.Rows[3]["SupplierPaintProductCode"].ToString() + ",";
myPrimer = myPrimer + myPrimers.Rows[3]["ColourName"].ToString();
}
}
rdrGet.Close();
cmdGet.Dispose();
}
catch (Exception ex)
{
myPrimer = string.Empty;
ErrorMessage = "Get Multi Coat Records - " + ex.Message;
}
return(myPrimer);
}
public static System.Data.DataTable SQLiteDataReader2Table(this System.Data.SQLite.SQLiteDataReader d)
{
//Create a new DataTable.
System.Data.DataTable dt = new System.Data.DataTable("Result");
//Load DataReader into the DataTable.
dt.Load(d);
return(dt);
}
/// <summary>
/// Method that uploads the data from the MySqlDataReader that contains the data.
/// </summary>
/// <param name="reader">Data reader that contains the source data that to be uploaded</param>
public void WriteToServer(System.Data.IDataReader reader)
{
//if (reader.HasRows)
//{
System.Data.DataTable table = new System.Data.DataTable();
table.Load(reader);
WriteToServer(table);
//}
}
/// <summary>
/// Method that uploads the data from the MySqlDataReader that contains the data.
/// </summary>
/// <param name="reader">Data reader that contains the source data that to be uploaded</param>
public void Upload(MySqlDataReader reader)
{
if (reader.HasRows)
{
System.Data.DataTable table = new System.Data.DataTable();
table.Load(reader);
Upload(table);
}
}
public static DataSet1 get_faktur(string no_faktur)
{
DataSet1 result = new DataSet1();
try
{
string[] table1 = { "pembayaran", "penjualan", "detail_penjualan" };
foreach (var table in table1)
{
string sql = string.Format("select * from {0} where no_faktur = '{1}'", table, no_faktur);
using (var db = new Models.sim_penjualanContext())
{
db.Database.Connection.Open();
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
result.Tables[table].Merge(dt);
}
}
}
}
string[] table2 = { "karyawan", "barang", "konsumen" };
foreach (var table in table2)
{
string sql = string.Format("select * from {0}", table);
using (var db = new Models.sim_penjualanContext())
{
db.Database.Connection.Open();
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
result.Tables[table].Merge(dt);
}
}
}
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(result);
}
private static int RunDataTable(SqlCeCommand cmd, SqlCeConnection conn)
{
cmd.Connection = conn;
System.Data.DataTable table = new System.Data.DataTable();
table.Locale = CultureInfo.InvariantCulture;
table.TableName = "data";
table.Load(cmd.ExecuteReader());
table.WriteXml(Console.Out, System.Data.XmlWriteMode.WriteSchema);
return(table.Rows.Count);
}
public static DataSet1 get_report(string column, string param)
{
DataSet1 result = new DataSet1();
try
{
string sql = "select * from barang";
column = column.ToLower();
if (column.Contains("kode"))
{
sql += string.Format(" where kd_barang like '%{0}%'", param);
}
else if (column.Contains("nama"))
{
sql += string.Format(" where nama like '%{0}%'", param);
}
else if (column.Contains("harga"))
{
sql += string.Format(" where harga like '%{0}%'", param);
}
else if (column.Contains("satuan"))
{
sql += string.Format(" where satuan like '%{0}%'", param);
}
else if (column.Contains("jenis"))
{
sql += string.Format(" where jenis like '%{0}%'", param);
}
else if (column.Contains("stok"))
{
sql += string.Format(" where stok like '%{0}%'", param);
}
using (var db = new Models.sim_penjualanContext())
{
db.Database.Connection.Open();
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
result.Tables["barang"].Merge(dt);
}
}
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(result);
}
public static DataSet1 get_report(string no_surat_jalan)
{
DataSet1 ds = new DataSet1();
try
{
using (var db = new Models.jotunContext())
{
db.Database.Connection.Open();
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = string.Format("select * from surat_jalan where no_surat_jalan = '{0}'", no_surat_jalan);
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
ds.Tables["surat_jalan"].Merge(dt);
}
cmd.CommandText = string.Format("select * from detail_surat_jalan where no_surat_jalan = '{0}'", no_surat_jalan);
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
ds.Tables["detail_surat_jalan"].Merge(dt);
}
cmd.CommandText = string.Format("select * from barang");
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
ds.Tables["barang"].Merge(dt);
}
cmd.CommandText = string.Format("select * from customer");
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
ds.Tables["customer"].Merge(dt);
}
}
}
}
catch (Exception ex)
{
Helpers.Generic.MsgEx(ex.Message);
}
return(ds);
}
public List <string> getDataBaseCliente(int dni)
{
List <string> lista = new List <string>();
SqlConnection connection;
SqlCommand command;
string sql = "";
SqlDataReader dataReader;
System.Data.DataTable dt = new System.Data.DataTable();
sql = "SELECT " +
"uId AS 'FORMA DE PAGO' " +
",[iNroY] AS 'NÚMERO DE Y' " +
",[iNroZ] AS 'NÚMERO DE Z' " +
",[iNroPos] AS 'NÚMERO DE POS' " +
",[iNroSuc] AS 'NÚMERO DE SUCURSAL' " +
",[iNroUser] AS 'LEGAJO CAJERO' " +
",[cDescripcion] AS 'DESCRIPCIÓN FORMA DE PAGO' " +
",REPLACE(CONVERT(VARCHAR,[dImporte]), '.', ',') AS 'IMPORTE' " +
",[dRecDesc] AS 'RECARGO/DESCUENTO' " +
",[cComprobante] AS 'COMPROBANTE' " +
",[cCliente] AS 'TARJETA CLIENTE' " +
",[cBarCode] AS 'PLAN TARJETA' " +
",[cPlan] AS 'CPLAN' " +
",[fTimeSynchro] AS 'FECHA Y HORA' " +
",[vData] AS 'DATOS DEL CLIENTE (TIPO TELEFONO, NRO DE TELEFONO, DNI)' " +
"FROM dE_DDMP " +
"WHERE vData LIKE '%" + dni + "%'";
connection = new SqlConnection(connectionString);
try
{
connection.Open();
command = new SqlCommand(sql, connection);
dataReader = command.ExecuteReader();
/*while (dataReader.Read())
* {*/
dt.Load(dataReader);
//}
dataReader.Close();
command.Dispose();
connection.Close();
}
catch (Exception ex)
{
dt = null;
}
lista.Add(dt.ToString());
return(lista);
}
override protected DataTables.Result _Exec()
{
var dt = new System.Data.DataTable();
using (var dr = _stmt.ExecuteReader())
{
dt.Load(dr);
}
return(new Sqlserver.Result(_db, dt, this));
}
private string sqlDatoToJson(SqlDataReader dataReader)
{
var dataTable = new System.Data.DataTable();
dataTable.Load(dataReader);
string JSONString = string.Empty;
JSONString = JsonConvert.SerializeObject(dataTable);
return(JSONString);
}
/// <summary>
/// Devuelve un datatable a partir de un excel, debemos seleccionar la pestaña e introducir la columna clave para eliminar filas en blanco
/// </summary>
/// <param name="strFile">Path al fichero</param>
/// <param name="sheetName">Pestaña de la hoja de calculos</param>
/// <param name="keyColumn">Columna clave se eliminaran todas las filas en las que esta casilla no este valorizada</param>
/// <returns></returns>
public static System.Data.DataTable GetDataTableFromExcel(string strFile, string sheetName, string keyColumn = "")
{
var con = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;TypeGuessRows=0;HDR=Yes;ImportMixedTypes=Text;'", strFile);
using (OleDbConnection connection = new OleDbConnection(con))
{
connection.Open();
var command = new OleDbCommand("Select * from [{0}$]".FormatWith(sheetName), connection);
if (keyColumn.HasValue())
command = new OleDbCommand("Select * from [{0}$] where {1} is not null".FormatWith(sheetName, keyColumn), connection);
using (OleDbDataReader dr = command.ExecuteReader())
{
var table = new System.Data.DataTable();
table.Load(dr);
return table;
}
}
}
public void FilterResults(Object sender, EventArgs a)
{
dbProcedures db = new dbProcedures();
try
{
System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM tbl_Members WHERE Firstname + ' ' + LastName like '%" + txtEmployeeName.Text + "%' AND EmployerGroup like '%" + ddlEmployerGroup.SelectedValue + "%' ORDER BY Username", new db().SqlConnection);
sqlCmd.CommandType = System.Data.CommandType.Text;
System.Data.DataTable loadReaderdata = new System.Data.DataTable();
loadReaderdata.Load(sqlCmd.ExecuteReader());
GridView1.DataSource = loadReaderdata;
GridView1.DataBind();
}
catch (Exception ex)
{
db.LogAction("", "Error", "Error loading Members UI page. " + ex.Message.ToString());
}
db.Close();
}
public void MemberSearch(string strUsername)
{
dbProcedures db = new dbProcedures();
try
{
System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM tbl_Members WHERE Username like '%" + strUsername + "%' ORDER BY Username", new db().SqlConnection);
sqlCmd.CommandType = System.Data.CommandType.Text;
System.Data.DataTable loadReaderdata = new System.Data.DataTable();
loadReaderdata.Load(sqlCmd.ExecuteReader());
GridView1.DataSource = loadReaderdata;
GridView1.DataBind();
}
catch (Exception ex)
{
db.LogAction("", "Error", "Error loading Members UI page. " + ex.Message.ToString());
}
db.Close();
}
public static System.Data.DataTable Obtener(string sql, params object[] parametros)
{
Conectar();
try
{
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.OleDb.OleDbCommand com = Comando(sql, parametros);
com.Transaction = transaccion;
System.Data.OleDb.OleDbDataReader r = com.ExecuteReader();
dt.Load(r);
return dt;
}
catch (System.Exception ex)
{
throw new System.Exception("Conexion: Error al obtener.", ex);
}
finally
{
Desconectar();
}
}
public override System.Data.DataTable ExecuteDataTable(string commandText, System.Data.CommandType commandType, ref System.Data.IDataParameter[] args, int commandTimeOut)
{
System.Data.DataTable results = new System.Data.DataTable();
SqlConnection connection = null;
if (dw_dbConn != null)
{
connection = dw_dbConn;
}
else
{
connection = new SqlConnection(connectionString);
}
try
{
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
using (SqlCommand cmd = new SqlCommand(commandText, connection))
{
if (commandTimeOut > 0)
{
cmd.CommandTimeout = commandTimeOut;
}
cmd.CommandType = commandType;
if (args != null && args.Length > 0)
{
cmd.Parameters.AddRange(args);
}
using (System.Data.IDataReader reader = cmd.ExecuteReader())
{
results.Load(reader, System.Data.LoadOption.OverwriteChanges);
}
}
}
finally
{
if (connection != null && connection != dw_dbConn)
{
connection.Close();
connection.Dispose();
}
}
return results;
}
public bool VendorCopyProduct(ArrayList execSql, ArrayList specs, string selMaster, string moveMaster, string movePrice)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(strConn);
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
}
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
foreach (string str in execSql)
{
mySqlCmd.CommandText = str;
mySqlCmd.ExecuteNonQuery();
}
SerialDao serialDao = new SerialDao("");
foreach (string str in specs)
{
mySqlCmd.CommandText = serialDao.Update(18);//規格編號
int specId = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.CommandText = string.Format(str, specId);
mySqlCmd.ExecuteNonQuery();
}
if (!string.IsNullOrEmpty(selMaster) && !string.IsNullOrEmpty(moveMaster) && !string.IsNullOrEmpty(movePrice))
{
mySqlCmd.CommandText = selMaster;
MySqlDataReader reader = mySqlCmd.ExecuteReader(System.Data.CommandBehavior.SingleResult);
System.Data.DataTable _dt = new System.Data.DataTable();
_dt.Load(reader);
if (_dt == null)
{
mySqlCmd.Transaction.Rollback();
return false;
}
else
{
foreach (System.Data.DataRow item in _dt.Rows)
{
mySqlCmd.CommandText = string.Format(moveMaster, item["price_master_id"]);
int masterId = Convert.ToInt32(mySqlCmd.ExecuteScalar());
mySqlCmd.CommandText = string.Format(movePrice, masterId, item["price_master_id"]);
mySqlCmd.ExecuteNonQuery();
}
}
}
mySqlCmd.Transaction.Commit();
return true;
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("ProductTempDao.VendorCopyProduct-->" + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}
/// <summary>
/// Get task from database
/// </summary>
/// <param name="task">Return result</param>
/// <param name="guid">Task unique guid</param>
/// <returns>Returns 'true' if operation was successful</returns>
public bool GetTask(out DbCommon.Task task, Guid guid)
{
bool result = false;
task = null;
System.Data.DataTable dt = new System.Data.DataTable();
try
{
if (this.OpenDb() &&
guid != null)
{
SQLiteCommand myCommand = new SQLiteCommand(_dbConnection);
myCommand.CommandText = String.Format("SELECT {0},{1},{2},{3} FROM {4} WHERE {5}='{6}'",
DbCommon.GUIN_COLUMN, // {0}
DbCommon.TEXT_COLUMN, // {1}
DbCommon.IMPORTANT_COLUMN, // {2}
DbCommon.URGENT_COLUMN, // {3}
DbCommon.TABLE_NAME, // {4}
DbCommon.GUIN_COLUMN, // {5}
guid.ToString()); // {6}
SQLiteDataReader reader = myCommand.ExecuteReader();
dt.Load(reader);
reader.Close();
result = _dbConnection.ResultCode() == SQLiteErrorCode.Ok;
this.CloseDb();
if (dt.Rows.Count > 0 &&
dt.Rows[0].ItemArray.Count() > 3)
{
task = new DbCommon.Task(dt.Rows[0].ItemArray[0].ToString(),
dt.Rows[0].ItemArray[1].ToString(),
dt.Rows[0].ItemArray[2].ToString(),
dt.Rows[0].ItemArray[3].ToString());
}
}
}
catch (Exception exc)
{
Console.WriteLine(exc.Message);
result = false;
}
return result;
}
/// <summary>
/// Method that uploads the data from the MySqlDataReader that contains the data.
/// </summary>
/// <param name="reader">Data reader that contains the source data that to be uploaded</param>
public void Upload(MySqlDataReader reader)
{
if (reader.HasRows)
{
System.Data.DataTable table = new System.Data.DataTable();
table.Load(reader);
Upload(table);
}
}
//TODO Lobby Changed. From
/*
public System.Data.DataTable SelectCards(string[] conditions)
{
var sb = new StringBuilder();
sb.Append("SELECT * FROM Card");
if (conditions != null)
{
string connector = " WHERE ";
foreach (string condition in conditions)
{
sb.Append(connector);
sb.Append("(");
sb.Append(condition);
sb.Append(")");
connector = " AND ";
}
}
using (var conn = new VistaDB.Provider.VistaDBConnection(db))
{
var cmd = new VistaDB.Provider.VistaDBCommand();
cmd.Connection = conn;
cmd.CommandText = sb.ToString();
var result = new System.Data.DataTable();
result.Load(cmd.ExecuteReader());
return result;
}
}
*/
//TO
public System.Data.DataTable SelectCards(string[] conditions)
{
var sb = new StringBuilder("SELECT * FROM Card");
List<String> sets = new List<String>();
if (conditions != null)
{
string connector;
connector = " ( ";
int i = 0;
bool foundSet = false;
sb = new StringBuilder();
foreach (string condition in conditions)
{
if (condition.Substring(0, 10).Equals("Card.setId"))
{
sb.Append(connector);
sb.Append("(");
sb.Append(condition);
sb.Append(")");
connector = " OR ";
conditions[i] = "";
foundSet = true;
}
i++;
}
sb.Append(")");
if (foundSet)
{
Array.Resize<string>(ref conditions, conditions.Length + 1);
conditions[conditions.Length - 1] = sb.ToString();
}
sb = new StringBuilder("SELECT * FROM Card");
connector = " WHERE ";
foreach (string condition in conditions)
{
if (!condition.Trim().Equals(""))
{
sb.Append(connector);
sb.Append("(");
sb.Append(condition);
sb.Append(")");
connector = " AND ";
}
}
}
using (var conn = new VistaDB.Provider.VistaDBConnection(db))
{
var cmd = new VistaDB.Provider.VistaDBCommand();
cmd.Connection = conn;
cmd.CommandText = sb.ToString();
var result = new System.Data.DataTable();
result.Load(cmd.ExecuteReader());
return result;
}
}
public HttpResponseMessage GetDailyLog(int page, double UTCOffset, int rows, string sidx, string sord, string filters = "", string devicefilters = "", string deviceIds = "")
{
//condition
string condition = null;
string condition2 = null; //存放KEYWARD條件
ArrayList al = null;
if (!string.IsNullOrEmpty(devicefilters))
{
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
jqGridUtil.Filters f = serializer.Deserialize<jqGridUtil.Filters>(devicefilters);
al = new System.Collections.ArrayList(f.rules.Count);
foreach (jqGridUtil.Rule rule in f.rules)
{
if (!string.IsNullOrEmpty(rule.data))
{
al.Add(jqGridUtil.GetSearchString(rule.field, rule.data, rule.op));
}
}
condition = string.Join(string.Format(" {0} ", f.groupOp), al.ToArray());
}
else if(!string.IsNullOrEmpty(deviceIds))
{
condition = string.Format("DeviceID in ({0})", deviceIds);
}
if (!string.IsNullOrEmpty(filters))
{
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
jqGridUtil.Filters f = serializer.Deserialize<jqGridUtil.Filters>(filters);
al = new System.Collections.ArrayList(f.rules.Count);
foreach (jqGridUtil.Rule rule in f.rules)
{
if (!string.IsNullOrEmpty(rule.data))
{
al.Add(jqGridUtil.GetSearchString(rule.field, rule.data, rule.op));
}
}
if ( al.Count>0)
{
//condition = string.Join(string.Format(" {0} ", f.groupOp), al.ToArray()) + " and " + condition;
condition2 = string.Join(string.Format(" {0} ", f.groupOp), al.ToArray());
}
}
//if (!string.IsNullOrEmpty(filters))
//{
// System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
// jqGridUtil.Filters f = serializer.Deserialize<jqGridUtil.Filters>(filters);
// al = new System.Collections.ArrayList(f.rules.Count);
// foreach (jqGridUtil.Rule rule in f.rules)
// {
// if (!string.IsNullOrEmpty(rule.data))
// {
// al.Add(jqGridUtil.GetSearchString(rule.field, rule.data, rule.op));
// }
// }
// condition += string.Join(string.Format(" {0} ", f.groupOp), al.ToArray());
//}
try
{
al = new ArrayList(1);
al.Add(condition);
al.Add(condition2);//新增車機過濾條件
int totalCount = DataProvider.Instance().GetCount_DailyLog(al);
al = new ArrayList();
al.Add(page);
al.Add(rows);
al.Add(sidx);
al.Add(sord);
al.Add(condition);
al.Add(condition2);
System.Data.IDataReader dr = DataProvider.Instance().FetchAll_DailyLog(al);
System.Data.DataTable dt = new System.Data.DataTable();
dt.Load(dr);
int totalPage = 0;
if (totalCount > 0)
{
totalPage = totalCount / rows;
if ((totalCount % rows) > 0) totalPage += 1;
}
else { totalPage = 0; }
if (page > totalPage) page = totalPage;
// 產生JSON 字串
List<Row> rowlist = new List<Row>() { };
int i = 0;
foreach (System.Data.DataRow item in dt.Rows)
{
Row rowobj = new Row();
var stringList = new List<string>();
rowobj.id = i;
stringList.Add(i.ToString());
stringList.Add(item["DeviceID"].ToString());
stringList.Add(item["AlertID"].ToString());
stringList.Add(item["CarNo"].ToString()); //將車牌改成車號
stringList.Add(item["DriverName"].ToString());
stringList.Add(item["DriverID"].ToString());
//stringList.Add(string.Format(Localization.GetString("DateTimeFormat", Localization.SharedResourceFile), Convert.ToDateTime(item["CreateDate"])));
stringList.Add(string.Format(Localization.GetString("DateTimeFormat", Localization.SharedResourceFile), Convert.ToDateTime(item["CreateDate"]).AddHours(UTCOffset)));
stringList.Add(item["RuleName"].ToString());
stringList.Add(item["AlarmTypeName"].ToString());
stringList.Add(item["SeverityCode"].ToString());
stringList.Add(string.Empty); //警報訊息
stringList.Add(item["HandleStatus"].ToString()); //工作狀態
stringList.Add(item["MisInfo"].ToString());
stringList.Add(item["AlarmType"].ToString());
stringList.Add(item["WorkListAlertID"].ToString());
//stringList.Add(item["GPS_Latitude"].ToString());
//stringList.Add(item["GPS_Longitude"].ToString());
stringList.Add(item["fleetSetting_WorkListID"].ToString());
rowobj.cell = stringList;
rowlist.Add(rowobj);
i = i + 1;
}
var jsonToReturn = new
{
total = totalPage.ToString(),
page = page.ToString(),
records = totalCount.ToString(),
rows = rowlist
};
return Request.CreateResponse(HttpStatusCode.OK, jsonToReturn);
}
catch (Exception)
{
throw;
}
}
public HttpResponseMessage GetDailyLogExcel(double UTCOffset, string filters = "", string cultureName = "")
{
Type objectType = Type.GetType("Keywin.DNN.Modules.Fleetmgm.fleetSetting_Company.ServicesController, Keywin.DNN.Modules.Fleetmgm.fleetSetting_Company", true, true);
Object objComponent = Activator.CreateInstance(objectType);
MethodInfo mi = objectType.GetMethod("GetPortalDeviceIdInternal");
int pID = PortalSettings.PortalId;
object[] param = { pID };
List<string> deviceIdList = (List<string>)mi.Invoke(objComponent, param);
string deviceIds = string.Empty;
foreach (string deviceId in deviceIdList)
{
deviceIds += ",'" + deviceId + "'";
}
//condition
string condition = null;
string condition2 = null; //存放KEYWARD條件
ArrayList al = null;
condition = string.Format("DeviceID in ({0})", deviceIds.TrimStart(','));
if (!string.IsNullOrEmpty(filters))
{
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
jqGridUtil.Filters f = serializer.Deserialize<jqGridUtil.Filters>(filters);
al = new System.Collections.ArrayList(f.rules.Count);
foreach (jqGridUtil.Rule rule in f.rules)
{
if (!string.IsNullOrEmpty(rule.data))
{
al.Add(jqGridUtil.GetSearchString(rule.field, rule.data, rule.op));
}
}
if (al.Count > 0)
{
condition2 = string.Join(string.Format(" {0} ", f.groupOp), al.ToArray());
}
}
try
{
al = new ArrayList();
al.Add(1);
al.Add(2147483647);
al.Add("CreateDate");
al.Add("desc");
al.Add(condition);
al.Add(condition2);
System.Data.IDataReader dr = DataProvider.Instance().FetchAll_DailyLog(al);
System.Data.DataTable dt = new System.Data.DataTable();
dt.Load(dr);
//EXCEL產生
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
//新增試算表
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("DailyLog");
//插入資料值
//表頭
HSSFRow title = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i <= 5; i++)
{
title.CreateCell(i).SetCellValue(Localization.GetString("Col" + i, "~/App_GlobalResources/List_DailyLogExcelColName." + cultureName + ".resx"));
}
if (dt.Rows.Count > 0)
{
//表身
int rowIndex = 1;
foreach (System.Data.DataRow item in dt.Rows)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(rowIndex++);
row.CreateCell(0).SetCellValue(item["CarNo"].ToString());
row.CreateCell(1).SetCellValue(item["DriverName"].ToString());
row.CreateCell(2).SetCellValue(string.Format(Localization.GetString("DateTimeFormat", Localization.SharedResourceFile), Convert.ToDateTime(item["CreateDate"]).AddHours(UTCOffset)));
row.CreateCell(3).SetCellValue(item["RuleName"].ToString());
row.CreateCell(4).SetCellValue(item["AlarmTypeName"].ToString());
row.CreateCell(5).SetCellValue(item["SeverityCode"].ToString());
}
}
else
{
HSSFRow row = (HSSFRow)sheet.CreateRow(1);
row.CreateCell(0).SetCellValue("Too many data, please use conditons");
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 6));
}
workbook.Write(ms);
HttpResponseMessage result = Request.CreateResponse(HttpStatusCode.OK);
result.Content = new ByteArrayContent(ms.ToArray());
result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
result.Content.Headers.ContentDisposition.FileName = HttpUtility.UrlEncode("DailyLog.xls", System.Text.Encoding.UTF8);
workbook = null;
ms.Close();
ms.Dispose();
return result;
}
catch (Exception ex)
{
return Request.CreateResponse(HttpStatusCode.InternalServerError, ex.Message);
}
}
public static System.Data.DataTable FromCache(this DataAccessComponents.DataAccessComponent q, DbCommand commands)
{
try
{
bool UseCache = false;
Boolean.TryParse(System.Configuration.ConfigurationManager.AppSettings["UseCache"],out UseCache);
System.Data.SqlClient.SqlCommand command = commands as System.Data.SqlClient.SqlCommand;
System.Data.DataTable objCache;
if (!UseCache)
{
objCache = new System.Data.DataTable();
objCache.Load(q.Database.ExecuteReader(command), System.Data.LoadOption.OverwriteChanges);
return objCache;
}
if (command == null) return null;
string CacheId = command.CommandText + "?";
foreach (System.Data.Common.DbParameter dbp in command.Parameters)
{
CacheId += dbp.ParameterName + "=" + dbp.Value.ToString() + "&";
}
//if (Environment.StackTrace.Contains("Administrator\\"))
//{
// objCache = q.ToList();
// return objCache;
//}
//else
{
// System.Threading.Thread.Sleep(500);
objCache = System.Web.HttpRuntime.Cache.Get(CacheId) as System.Data.DataTable;
}
if (objCache == null)
{
List<string> tablesNames = q.TableNames; //command.Mapping.GetTables().Where(t => (t.TableNames.Contains("[")) ? CacheId.Contains(t.TableNames.Substring(4)) : CacheId.Contains("[" + t.TableNames.Substring(4) + "]")).Select(t => t.TableNames.Substring(4)).ToList();
string connStr = q.Database.ConnectionString; // System.Configuration.ConfigurationManager.ConnectionStrings["MainDB"].ConnectionString;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr))
{
conn.Open();
System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(connStr);
System.Web.Caching.SqlCacheDependency sqldep;
AggregateCacheDependency aggDep = new AggregateCacheDependency();
foreach (string tableName in tablesNames)
{
if (!System.Web.Caching.SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(tableName))
System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, tableName);
if (tableName.Contains("Comment") || tableName.Contains("FormBuilder"))
sqldep = new System.Web.Caching.SqlCacheDependency("NatiqNewsPoll", tableName);
else
sqldep = new System.Web.Caching.SqlCacheDependency("NatiqNews", tableName);
aggDep.Add(sqldep);
}
objCache = new System.Data.DataTable();
objCache.Load(q.Database.ExecuteReader(command), System.Data.LoadOption.OverwriteChanges);
if (objCache != null)
System.Web.HttpRuntime.Cache.Insert(CacheId, objCache, aggDep, DateTime.Now.AddDays(1), System.Web.Caching.Cache.NoSlidingExpiration);
}
}
//Return the created (or cached) List
return objCache;
}
catch (Exception ex)
{
throw ex;
}
}
private static QueryData GetQueryData(string procName, string validName, string selectText, string descText, string number, string execText, string projectName, SqlConnection conn)
{
Log.Write("Start GetQueryData:" + execText);
var obj = new QueryData();
try
{
var lstColumns = new List<string>();
var sql = execText;
var cmd = new SqlCommand(sql.Replace("\\r\\n", "").Replace("\\r", ""), conn);
if (cmd.CommandText.StartsWith("EXEC"))
{
cmd.CommandTimeout = 200;
}
if (cmd.CommandText.Contains("check_patient"))
{
cmd.CommandTimeout = 90;
}
cmd.CommandTimeout = 200;
var rdrQD = cmd.ExecuteReader();
if (rdrQD.HasRows)
{
for (int i = 0; i < rdrQD.FieldCount; i++)
{
lstColumns.Add(rdrQD.GetName(i));
}
obj.FieldsName = lstColumns;
// Получаем сами данные
var dtData = new DataTable();
dtData.Load(rdrQD);
obj.Data = dtData;
obj.ProjectName = projectName;
obj.ValidationRule = validName;
obj.Description = descText;
obj.NameList = number;
}
else
{
obj = null;
}
rdrQD.Close();
rdrQD = null;
}
catch (Exception ex)
{
Log.Write(ex);
}
finally
{
Log.Write("Exit GetQueryData:" + execText);
}
return obj;
}
public override System.Data.DataTable ExecuteDataTable(String commandText, System.Data.CommandType commandType, ref System.Data.IDataParameter[] args, Int32 commandTimeOut)
{
System.Data.DataTable results = new System.Data.DataTable();
OracleConnection connection = null;
if (dw_dbConn != null)
{
connection = dw_dbConn;
}
else
{
connection = new OracleConnection(connectionString);
}
try
{
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
using (OracleCommand cmd = new OracleCommand(commandText, connection))
{
if (commandTimeOut > 0)
{
cmd.CommandTimeout = commandTimeOut;
}
cmd.CommandType = commandType;
DeriveParameters(cmd);
AssignParameters(cmd, ref args);
using (OracleDataReader reader = cmd.ExecuteReader())
{
results.Load(reader, System.Data.LoadOption.OverwriteChanges);
}
}
}
finally
{
if (connection != null && connection != dw_dbConn)
{
connection.Close();
connection.Dispose();
}
}
return results;
}
public System.Data.DataTable SelectCards(string[] conditions)
{
var sb = new StringBuilder();
sb.Append("SELECT * FROM Card");
if(conditions != null)
{
string connector = " WHERE ";
foreach(string condition in conditions)
{
sb.Append(connector);
sb.Append("(");
sb.Append(condition);
sb.Append(")");
connector = " AND ";
}
}
using(var conn = new VistaDB.Provider.VistaDBConnection(db))
{
var cmd = new VistaDB.Provider.VistaDBCommand();
cmd.Connection = conn;
cmd.CommandText = sb.ToString();
var result = new System.Data.DataTable();
result.Load(cmd.ExecuteReader());
return result;
}
}
public virtual void ImportarTabla(MapaDeTabla mapa)
{
Progreso.Value = 0;
Progreso.ChangeStatus("Leyendo la tabla " + mapa.ToString());
string SqlSelect = @"SELECT * FROM " + mapa.TablaExterna;
if (mapa.Where != null)
SqlSelect += " WHERE " + mapa.Where;
// Hago un SELECT de la tabla
System.Data.IDbCommand TableCommand = ConexionExterna.CreateCommand();
TableCommand.CommandText = SqlSelect;
System.Data.DataTable ReadTable = new System.Data.DataTable();
ReadTable.Locale = System.Globalization.CultureInfo.CurrentCulture;
ReadTable.Load(TableCommand.ExecuteReader());
if (mapa.AutoSaltear) {
mapa.Saltear = Lfx.Workspace.Master.CurrentConfig.ReadGlobalSetting<int>("Importar.RegistrosImportados." + this.Nombre + "." + mapa.Nombre, 0);
if (mapa.Saltear > 1)
mapa.Saltear--;
}
System.Data.IDbTransaction Trans = this.Connection.BeginTransaction();
// Navegar todos los registros
Progreso.ChangeStatus("Incorporando " + ReadTable.Rows.Count.ToString() + " registros de la tabla " + mapa.ToString());
Progreso.Max = ReadTable.Rows.Count;
int RowNumber = 0;
foreach (System.Data.DataRow OriginalRow in ReadTable.Rows) {
++RowNumber;
if (mapa.Saltear == 0 || RowNumber > mapa.Saltear) {
Lfx.Data.Row ProcessedRow = this.ProcesarRegistro(mapa, OriginalRow);
this.ImportarRegistro(mapa, ProcessedRow);
}
if ((RowNumber % 200) == 0) {
Progreso.Advance(200);
Lfx.Workspace.Master.CurrentConfig.WriteGlobalSetting("Importar.RegistrosImportados." + this.Nombre + "." + mapa.Nombre, RowNumber);
Trans.Commit();
Trans.Dispose();
Trans = this.Connection.BeginTransaction();
}
if (mapa.Limite > 0 && RowNumber > mapa.Limite)
break;
}
Trans.Commit();
Trans.Dispose();
Trans = null;
Lfx.Workspace.Master.CurrentConfig.WriteGlobalSetting("Importar.RegistrosImportados." + this.Nombre + "." + mapa.Nombre, RowNumber);
}
/// <summary>
/// Get tasks from database
/// </summary>
/// <param name="important">If tasks are important</param>
/// <param name="urgent">If tasks are urgent</param>
/// <param name="tasks">Return result</param>
/// <returns>Returns 'true' if operation was successful</returns>
public bool GetAllTasks(out List<DbCommon.Task> tasks, bool? important = null, bool? urgent = null)
{
bool result = false;
tasks = new List<DbCommon.Task>();
System.Data.DataTable dt = new System.Data.DataTable();
try
{
if (this.OpenDb())
{
string where = String.Empty;
if (important != null)
{
where = String.Format(" WHERE {0}='{1}'",
DbCommon.IMPORTANT_COLUMN, // {0}
important.Value ? "1" : "0");// {1}
}
if (urgent != null)
{
if (!String.IsNullOrWhiteSpace(where))
{
where = String.Format("{0} AND ",
where);
}
else
{
where = " WHERE ";
}
where = String.Format("{0}{1}='{2}'",
where, // {0}
DbCommon.URGENT_COLUMN, // {1}
urgent.Value ? "1" : "0");// {2}
}
SQLiteCommand myCommand = new SQLiteCommand(_dbConnection);
myCommand.CommandText = String.Format("SELECT {0},{1},{2},{3} FROM {4}{5}",
DbCommon.GUIN_COLUMN, // {0}
DbCommon.TEXT_COLUMN, // {1}
DbCommon.IMPORTANT_COLUMN, // {2}
DbCommon.URGENT_COLUMN, // {3}
DbCommon.TABLE_NAME, // {4}
where); // {5}
SQLiteDataReader reader = myCommand.ExecuteReader();
dt.Load(reader);
reader.Close();
result = _dbConnection.ResultCode() == SQLiteErrorCode.Ok;
this.CloseDb();
IEnumerator<System.Data.DataRow> enumerator = (IEnumerator<System.Data.DataRow>)dt.Rows.GetEnumerator();
while(enumerator.MoveNext())
{
if (enumerator.Current.ItemArray.Count() > 3)
{
DbCommon.Task task = new DbCommon.Task(enumerator.Current.ItemArray[0].ToString(),
enumerator.Current.ItemArray[1].ToString(),
enumerator.Current.ItemArray[2].ToString(),
enumerator.Current.ItemArray[3].ToString());
tasks.Add(task);
}
}
}
}
catch (Exception exc)
{
Console.WriteLine(exc.Message);
result = false;
}
return result;
}