public static string SP_Tender_FindRecord(string ProcessId, ref DataSet ReturnDs)
{
SqlConnection sqlConn = new SqlConnection(); //defines database connection
SqlCommand sqlCmd = new SqlCommand(); //defines what to do
SqlDataAdapter sqlAdap = new SqlDataAdapter();
try
{
sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings["AMS_MasterConnectionString"].ToString();
sqlConn.Open();
sqlCmd.CommandText = "SP_Tender_FindRecord";
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Connection = sqlConn;
SqlParameter parm1 = new SqlParameter("@ProcessId", SqlDbType.VarChar);
parm1.Value = ProcessId;
parm1.Direction = ParameterDirection.Input;
sqlCmd.Parameters.Add(parm1);
sqlAdap.SelectCommand = sqlCmd;
sqlAdap.Fill(ReturnDs);
return string.Empty;
}
catch (Exception err)
{
return err.Message;
}
finally
{ sqlConn.Close(); sqlConn.Dispose(); sqlAdap.Dispose(); }
}
public DataSet FilterData(ArsonFilterModel filterData)
{
var connectionString = ConfigurationManager.ConnectionStrings["UCR_DataEntities"];
DataSet dsResult = new DataSet();
using (SqlConnection conn = new SqlConnection(connectionString.ConnectionString))
{
try
{
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = GenerateArsonORQuery(filterData);
command.CommandType = System.Data.CommandType.Text;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
conn.Open();
adapter.Fill(dsResult);
conn.Close();
adapter.Dispose();
command.Dispose();
}
catch (Exception ex)
{
StorageClient.LogError(ex);
}
}
return dsResult;
}
/// <summary>
/// Вывести в ds результат запроса select из command
/// В случае ошибки вывести код ошибки.
/// </summary>
/// <param name="command"></param>
/// <param name="ds"></param>
/// <param name="ErrMsg"></param>
/// <returns></returns>
public static SqlState SqlViewTable(string Command, ref DataSet ResultDS, ref string ErrMsg)
{
SqlDataAdapter sql = new SqlDataAdapter(Command, ConnectString);
ResultDS = new DataSet();
try
{
int count = sql.Fill(ResultDS);
sql.Dispose();
if (count > 0)
return SqlState.Success;
else
{
ErrMsg = "Запрос не вернул ни одну запись.";
return SqlState.EmptyResult;
}
}
catch (Exception exp)
{
sql.Dispose();
ErrMsg = "Method: " + exp.TargetSite.Name.ToString() + "\r\nMessage: " + exp.Message + "\r\nSource: " + exp.Source + "\r\nCommand: " + Command;
MessageBox.Show(ErrMsg, "SqlViewTable() Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return SqlState.Error;
}
finally
{
sql.Dispose();
}
}
public static void NewBlogSubscribers()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlCommand command = null;
DataSet insertedDS = null;
SqlDataAdapter dataAdapter = null;
try
{
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;",
connection);
dataAdapter = new SqlDataAdapter(command);
insertedDS = new DataSet();
dataAdapter.Fill(insertedDS);
TriggerHandler(connection, ContentType.Blog, insertedDS);
}
}
catch { }
finally
{
try
{
if (command != null)
{
command.Dispose();
command = null;
}
if (dataAdapter != null)
{
dataAdapter.Dispose();
dataAdapter = null;
}
if (dataAdapter != null)
{
dataAdapter.Dispose();
dataAdapter = null;
}
if (insertedDS != null)
{
insertedDS.Dispose();
insertedDS = null;
}
}
catch { }
}
}
/// <summary>
/// Metodo que consulta todos los Conductores
/// </summary>
/// <returns>Retorna un DataTable con todos los Conductores</returns>
public DataTable ConsultarConductores()
{
SqlCommand commandData = new SqlCommand();
System.Data.DataSet ds = new System.Data.DataSet();
DataTable dt = new DataTable();
OpenData("query");
string sqlconsulta = " SELECT * FROM [Conductor] with(nolock) ";
commandData = new System.Data.SqlClient.SqlCommand(sqlconsulta, sqlConnData);
commandData.CommandType = CommandType.Text;
SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(commandData);
try
{
da.Fill(dt);
}
catch (Exception)
{
ds = null;
}
finally
{
da.Dispose();
}
CloseData();
return(dt);
}
/// <summary>
/// Metodo para consulta del historial de un viaje especifico
/// </summary>
/// <param name="id_viaje"></param>
/// <returns>Retorna un DataTable con todos los registros</returns>
public DataTable ConsultarViajesTracking(string id_viaje)
{
SqlCommand commandData = new SqlCommand();
System.Data.DataSet ds = new System.Data.DataSet();
DataTable dt = new DataTable();
OpenData("query");
string sqlconsulta = "SELECT [Id_viaje],[Ubicacion],[Observaciones] FROM [dbo].[Tracking] where Id_viaje = @Id_Viaje";
commandData = new System.Data.SqlClient.SqlCommand(sqlconsulta, sqlConnData);
commandData.CommandType = CommandType.Text;
commandData.Parameters.Add("@Id_Viaje", System.Data.SqlDbType.VarChar, 50);
commandData.Parameters["@Id_Viaje"].Value = id_viaje.Trim();
//agregamos el parametro al query con el valor requerido
SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(commandData);
try
{
da.Fill(dt);
}
catch (Exception ex)
{
string test = ex.Message;
ds = null;
}
finally
{
da.Dispose();
}
CloseData();
return(dt);
}
public DataSet GetDataSet(string a_strDataSource) //Select query
{
System.Data.SqlClient.SqlDataAdapter myAdapter;
DataSet DS;
try
{
DS = new DataSet();
myAdapter = new System.Data.SqlClient.SqlDataAdapter(a_strDataSource, GetConnectionString());
myAdapter.SelectCommand.CommandTimeout = ConnectionTimeOut;
myAdapter.Fill(DS);
myAdapter.Dispose();
myAdapter = null;
return(DS);
}
catch (Exception ex)
{
ex.Data.Clear();
return(null);
}
finally
{
//DS.Dispose();
}
}
//---------------------- methods ----------------------
public DataSet _dataSet_NoParameter(string queryString)
{
try
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = connectionString;
if (conn.State == ConnectionState.Closed) conn.Open();
SqlCommand comm = new SqlCommand(queryString, conn);
daRecords = new SqlDataAdapter(comm);
daRecords.Fill(dsRecords);
return dsRecords;
}
catch (SqlException er)
{
sqlUserError = er.Message;
return dsRecords;
}
finally
{
dtRecords.Dispose();
daRecords.Dispose();
dsRecords.Dispose();
}
}
public DataTable load_country()
{
try
{
if (care.State == ConnectionState.Closed)
{
care.Open();
}
adapt = new SqlDataAdapter("fetchCountry", care);
adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
adapt.Fill(ds, "tblCountry");
adapt.Dispose();
return ds.Tables["tblCountry"];
}
catch
{
throw;
}
finally
{
care.Close();
}
}
/// <summary>
/// 获取某个用户及其下级
/// </summary>
/// <param name="NReturn"></param>
/// <param name="ExMsg"></param>
/// <param name="UserID">UserID</param>
/// <param name="ContainsSelf">是否包含自己</param>
/// <param name="ContainsGrand">是否包含子孙</param>
/// <returns></returns>
public static DataTable ListChild(ref int NReturn, ref string ExMsg, long UserID, bool ContainsSelf, bool ContainsGrand)
{
DataSet ds = new DataSet();
using (SqlConnection SqlConn = new SqlConnection(Apq.DB.GlobalObject.SqlConnectionString))
{
SqlDataAdapter sda = new SqlDataAdapter("dtxc.Apq_Users_ListChild", SqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sda.SelectCommand);
dch.AddParameter("rtn", 0, DbType.Int32);
dch.AddParameter("ExMsg", ExMsg, DbType.String, -1);
dch.AddParameter("UserID", UserID, DbType.Int64);
dch.AddParameter("ContainsSelf", ContainsSelf, DbType.Byte);
dch.AddParameter("ContainsGrand", ContainsGrand, DbType.Byte);
sda.SelectCommand.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
sda.SelectCommand.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sda.Fill(ds);
NReturn = System.Convert.ToInt32(sda.SelectCommand.Parameters["rtn"].Value);
ExMsg = sda.SelectCommand.Parameters["ExMsg"].Value.ToString();
sda.Dispose();
SqlConn.Close();
}
return ds.Tables.Count > 0 ? ds.Tables[0] : null;
}
public static DataSet ExecuteDataset(SqlConnection cn, SqlTransaction trans, string cmdText, string tableName, params SqlParameter[] sqlParams)
{
DataSet data = new DataSet();
SqlCommand cmd = new SqlCommand(cmdText, cn);
cmd.CommandType = CommandType.Text;
cmd.Transaction = trans;
if (sqlParams != null)
{
AttachParameters(cmd, sqlParams);
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
if (tableName != null && tableName != string.Empty)
{
adapter.Fill(data, tableName);
}
else
{
adapter.Fill(data);
}
adapter.Dispose();
cmd.Parameters.Clear();
cmd.Dispose();
return data;
}
public static DataSet ExecuteQuery(string sql)
{
try
{
//string connString = "Data Source=.\\SQLExpress; Integrated Security=true; User Instance=true; Initial Catalog=master;";
string connString = "Server=.\\SQLEXPRESS;Database=master;Integrated Security=SSPI;";
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
con.ConnectionString = connString; //DBBase.GetConnectionString();
con.Open();
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(sql, con);
DataSet ds1 = new DataSet();
da.Fill(ds1);
da.Dispose();
con.Close();
con.Dispose();
return ds1;
}
catch (Exception ex)
{
Logger.LogQuery("SQL Exception:" + Environment.NewLine + Environment.NewLine + sql + Environment.NewLine + Environment.NewLine + ex.ToString(), true, true);
throw;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["kullanici"] != null || Session["yonetici"] != null)
{
}
else
{
Response.Redirect("KullaniciGiris.aspx");
}
try
{
Yardimci.baglanti.Open();
SqlCommand ogretmen_cek = new SqlCommand();
ogretmen_cek.CommandText = "SELECT * FROM OGRETMENLER_VIEW2";
ogretmen_cek.Connection = Yardimci.baglanti;
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter(ogretmen_cek);
adp.Fill(ds, "OGRETMENLER_VIEW2");
dwOgretmenler.DataSource = ds.Tables["OGRETMENLER_VIEW2"];
dwOgretmenler.DataBind();
ds.Dispose();
adp.Dispose();
Yardimci.baglanti.Close();
}
catch (Exception hata)
{
Yardimci.baglanti.Close();
lbl_sonuc.Text = hata.Message;
}
}
// To get 'Curriculum' record of both 'Active','Inactive' type from database by stored procedure
public DataTable LoadCurriculum(int LoggedInUser, string Ret)
{
SqlConnection Conn = new SqlConnection(ConnStr);
// 'uspGetIntakeFormCurriculum' stored procedure is used to get both 'Active','Inactive' type records from Curriculum table
SqlDataAdapter DAdapter = new SqlDataAdapter("Transactions.uspGetIntakeFormCurriculum", Conn);
DAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
DAdapter.SelectCommand.Parameters.AddWithValue("@LoggedInUser", LoggedInUser);
DAdapter.SelectCommand.Parameters.AddWithValue("@RetMsg", Ret);
DataSet DSet = new DataSet();
try
{
DAdapter.Fill(DSet, "Curriculum");
return DSet.Tables["Curriculum"];
}
catch
{
throw;
}
finally
{
DSet.Dispose();
DAdapter.Dispose();
Conn.Close();
Conn.Dispose();
}
}
//district
public DataTable load_districts(Int16 stateId)
{
try
{
if (care.State == ConnectionState.Closed)
{
care.Open();
}
adapt = new SqlDataAdapter("fetchDist", care);
adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
adapt.SelectCommand.Parameters.AddWithValue("@stateId", stateId);
DataSet ds = new DataSet();
adapt.Fill(ds, "tblDist");
adapt.Dispose();
return ds.Tables["tblDist"];
}
catch
{
throw;
}
finally
{
care.Close();
}
}
private void timtheotenbn() // Tìm kiếm dữ liệu theo mã
{
Ketnoi(); // Tạo kết nối file Access
// Tạo Command
SqlCommand objcommand = new SqlCommand();
objcommand.Connection = objcon;
objcommand.CommandType = CommandType.Text;
objcommand.CommandText = "Select * From tbbenhnhan Where tenbn LIKE '%" + txttimtheotenbn.Text + "%' ORDER BY tenbn";
//Tạo đối tượng Adapter
SqlDataAdapter objAdapter = new SqlDataAdapter();
objAdapter.SelectCommand = objcommand;
// Tạo DataTable nhận dữ liệu trả về
DataTable objDataTable = new DataTable("abcd");
objAdapter.Fill(objDataTable);
// Gán dữ liệu vào dataGrid
dgwtim.DataSource = objDataTable;
// Hủy các đối tượng
objcommand.Dispose();
objcommand = null;
objDataTable.Dispose();
objDataTable = null;
objAdapter.Dispose();
objAdapter = null;
// hủy kết nối
}
public static DataTable getDropDownData(string tblFields, string tblName, string whereCond)
{
try
{
SqlCommand CMD = new SqlCommand();
CMD.Connection = ILODBConfig.GetConnection();
CMD.CommandType = CommandType.Text;
CMD.CommandText = "Select " + tblFields + " From " + tblName + " where " + whereCond;
SqlDataAdapter SDA = new SqlDataAdapter(CMD);
DataTable DT = new DataTable();
SDA.Fill(DT);
ILODBConfig.GetConnection().Dispose();
CMD.Connection.Dispose();
CMD = null;
SDA.Dispose();
return DT;
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
ILODBConfig.GetConnection().Close();
}
}
public System.Data.DataSet ExecuteDataset(string sql)
{
var ds = new System.Data.DataSet();
var cmd = new System.Data.SqlClient.SqlCommand(sql, _connection);
cmd.CommandTimeout = 700;
System.Data.SqlClient.SqlDataAdapter da;
try
{
OpenConnection();
//da = new SqlDataAdapter(sql, _connection);
da = new System.Data.SqlClient.SqlDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
CloseConnection();
}
catch (Exception ex)
{
throw ex;
}
finally
{
da = null;
CloseConnection();
}
return(ds);
}
ImportTemplateSettings TemplateSettings = new ImportTemplateSettings(); //application import settings
public UpdateImportDatasetForQC(DataSet ds, ImportTemplateSettings ts)
{
DataForQCDataSet = new DataSet(); //start with clean dataset
DataForQCDataSet = ds.Copy(); //Data for QC selection from the main data dataset
TemplateSettings = ts; //Template settings
//Get the list of pallets to QC
string QCconnString = Properties.Settings.Default.ArchiveConnectionString;
StringBuilder QCCommand_Text = new StringBuilder();
if (Properties.Settings.Default.Mode.ToString() == "Test")
{
QCquery = "select [Pallet_Number] FROM [QC_Archive_Test]"; // List of pallets for QC from test table;
}
else
{
QCquery = "select [Pallet_Number] FROM [QC_Archive]"; // List of pallets for QC;
}
System.Data.SqlClient.SqlConnection QCconn = new System.Data.SqlClient.SqlConnection(QCconnString);
System.Data.SqlClient.SqlCommand QCcmd = new System.Data.SqlClient.SqlCommand(QCquery, QCconn);
QCconn.Open();
DataSet QCDataSet = new DataSet();
// create data adapter
System.Data.SqlClient.SqlDataAdapter QCda = new System.Data.SqlClient.SqlDataAdapter(QCcmd);
// this will query the database and return the result to your datatable
QCda.Fill(QCDataSet);
QCconn.Close();
QCda.Dispose();
QCTable = QCDataSet.Tables[0]; //set the translation table
QCDataSet.Dispose();
}
protected void Page_Load(object sender, EventArgs e)
{
try {
string componentName = Request["componentName"];
string tokenName = Request["tokenName"];
string tokenValue = string.Empty;
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand("dbo.FSFPL_RestoreEditableContent");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("ComponentName", componentName);
cmd.Parameters.AddWithValue("TokenName", tokenName);
SqlConnection conn = new SqlConnection(SiteConfig.ConnectionString);
cmd.Connection = conn;
SqlDataAdapter sqlda = new SqlDataAdapter(cmd);
sqlda.Fill(ds);
// return data for display
tokenValue = ds.Tables[0].Rows[0]["TokenValue"].ToString();
// cleanup
conn.Close();
ds.Dispose();
cmd.Dispose();
sqlda.Dispose();
Response.Write(tokenValue);
} catch (Exception ex) {
Response.Write(ex.Message);
}
}
public DataTable getData()
{
try
{
query = "PARTY_FETCHING";
if (con.State == ConnectionState.Closed)
{
con.Open();
}
dap = new SqlDataAdapter(query, con);
dap.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
dap.Fill(ds, "temp");
dap.Dispose();
return ds.Tables["temp"];
}
catch
{
throw;
}
finally
{ if(con.State == ConnectionState.Open)
con.Close();
}
}
private void loginButton_Click(object sender, RoutedEventArgs e)
{
string connetionString = null;
SqlConnection connection;
SqlCommand command ;
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet ds = new DataSet();
string sql = null;
connetionString = "Data Source=130.237.226.220;Initial Catalog=starwars;User ID=charizard;Password=polka123";
sql = "SELECT * from Users where login="******"and password = "******";";
connection = new SqlConnection(connetionString);
try
{
connection.Open();
command = new SqlCommand(sql, connection);
adapter.SelectCommand = command;
adapter.Fill(ds, "SQL Temp Table");
adapter.Dispose();
command.Dispose();
connection.Close();
foreach (DataTable tables in ds.Tables)
{
MessageBox.Show (tables.TableName);
}
}
catch (Exception ex)
{
MessageBox.Show("Can not open connection ! ");
}
}
public DataTable getData(partyBO PartyBO)
{
try
{
query = "fetchParty";
if (con.State == ConnectionState.Closed)
{
con.Open();
}
dap = new SqlDataAdapter(query, con);
dap.SelectCommand.CommandType = CommandType.StoredProcedure;
dap.SelectCommand.Parameters.AddWithValue("@countryId", PartyBO.countryId);
DataSet ds = new DataSet();
dap.Fill(ds, "temp");
dap.Dispose();
return ds.Tables["temp"];
}
catch
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
public System.Data.DataSet ExecuteDataset(string sql)
{
var ds = new System.Data.DataSet();
var cmd = new System.Data.SqlClient.SqlCommand(sql, _connection);
cmd.CommandTimeout = 120;
System.Data.SqlClient.SqlDataAdapter da;
try
{
OpenConnection();
//da = new SqlDataAdapter(sql, _connection);
da = new System.Data.SqlClient.SqlDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
CloseConnection();
}
catch (Exception ex)
{
//throw ex;
DataTable objDt1 = new DataTable();
objDt1.Columns.Add("Code");
objDt1.Columns.Add("Message");
objDt1.Columns.Add("id");
objDt1.Rows.Add(1, ConfigurationManager.AppSettings["phase"] != null && ConfigurationManager.AppSettings["phase"].ToString().ToUpper() == "DEVELOPMENT" ? ex.Message : "Something went wrong.", "");
ds.Tables.Add(objDt1);
}
finally
{
da = null;
CloseConnection();
}
return(ds);
}
public System.Data.DataTable sdatatable(string query)
{
System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection();
cn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString;
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query);
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = cn;
try
{
cn.Open();
sda.SelectCommand = cmd;
try
{
sda.Fill(dt);
}
catch { }
cn.Close();
cn.Dispose();
}
finally
{
cn.Close();
sda.Dispose();
cn.Dispose();
}
return(dt);
}
/// <summary>
/// ִ�д洢���̻�SQL��䣬������һ��DataTable
/// </summary>
/// <param name="strProcedureName">Ҫ��DataTable�Ĵ洢���̻�SQL���</param>
/// <returns></returns>
public DataTable ExcuteProcedure(string strProcedureName)
{
DataTable dt = new DataTable();
SqlConnection conn=null;
try
{
conn = new SqlConnection(ConfigurationSettings.AppSettings.GetValues("connection")[0]);
SqlDataAdapter sda = new SqlDataAdapter(strProcedureName, conn);
DataSet ds = new DataSet();
conn.Open();
sda.Fill(ds);
dt = ds.Tables[0];
sda.Dispose();
conn.Close();
conn.Dispose();
}
catch (Exception ex)
{
Err MyErr = new Err(ex.Message.ToString());
}
return dt;
}
public static string GetProcessId(ref string ProcessId, string TenderNumber)
{
SqlConnection sqlConn = new SqlConnection(); //defines database connection
SqlCommand sqlCmd = new SqlCommand(); //defines what to do
SqlDataAdapter sqlAdap = new SqlDataAdapter();
try
{
sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings["AMS_MasterConnectionString"].ToString();
sqlConn.Open();
sqlCmd.CommandText = "SELECT [TH_ProcessID] FROM [AMS_Master].[dbo].[Tender_Header] H WHERE [TH_NoTender] = '" + TenderNumber + "'";
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Connection = sqlConn;
ProcessId = sqlCmd.ExecuteScalar().ToString();
return string.Empty;
}
catch (Exception err)
{
return err.Message;
}
finally
{ sqlConn.Close(); sqlConn.Dispose(); sqlAdap.Dispose(); }
}
public static DataSet ExecuteDataset(SqlConnection cn, SqlTransaction trans, string cmdText, string tableName, params SqlParameter[] sqlParams)
{
DataSet data = new DataSet();
try
{
SqlCommand cmd = new SqlCommand(cmdText, cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = trans;
if (sqlParams != null)
{
AttachParameters(cmd, sqlParams);
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
if (tableName != null && tableName != string.Empty)
{
adapter.Fill(data, tableName);
}
else
{
adapter.Fill(data);
}
adapter.Dispose();
cmd.Parameters.Clear();
cmd.Dispose();
}
catch (Exception err)
{
TScope.HandlError(err);
}
return data;
}
protected void LinkButtonOffers_Click(object sender, EventArgs e)
{
PanelUsers.Visible = false;
PanelOffers.Visible = true;
PanelCredit.Visible = false;
PanelCoupons.Visible = false;
LinkButtonUsers.Enabled = true;
LinkButtonOffers.Enabled = false;
LinkButtonCredit.Enabled = true;
LinkButtonCoupons.Enabled = true;
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ShopConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_statsOffers", sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.Fill(ds);
dt = ds.Tables[0];
TimeClass tc = new TimeClass();
LabelOffersDate.Text = tc.ConvertToIranTimeString(Convert.ToDateTime(dt.Rows[0]["OffersDate"].ToString()));
LabelOffersOffers.Text = dt.Rows[0]["OffersCount"].ToString();
LabelOffersActive.Text = dt.Rows[0]["OffersActive"].ToString();
LabelOffersPast.Text = dt.Rows[0]["OffersPast"].ToString();
LabelOffersSold.Text = dt.Rows[0]["OffersSoldCount"].ToString();
LabelOffersAverage.Text = (Convert.ToInt32(dt.Rows[0]["OffersSoldCount"].ToString()) / Convert.ToInt32(dt.Rows[0]["OffersPast"].ToString())).ToString();
sda.Dispose();
sqlConn.Close();
}
public DataTable getIssues(Int64 NUMBER,Int16 TYPE)
{
try
{
query = "ISSUES_FETCHING";
if (con.State == ConnectionState.Closed)
{
con.Open();
}
dap = new SqlDataAdapter(query,con);
dap.SelectCommand.CommandType = CommandType.StoredProcedure;
dap.SelectCommand.Parameters.AddWithValue("@number",NUMBER) ;
dap.SelectCommand.Parameters.AddWithValue("@type", TYPE);
DataSet ds = new DataSet();
dap.Fill(ds,"temp");
dap.Dispose();
return ds.Tables["temp"];
}
catch
{
throw;
}
finally
{ if(con.State == ConnectionState.Open)
con.Close();
}
}
// To get 'Program Lead(' record of 'Active' or 'Inactive' from database by stored procedure
public DataTable LoadActiveProgramLead(int LoggedInUser, string returnmsg, bool IsActive)
{
SqlConnection Conn = new SqlConnection(ConnStr);
// 'uspGetProgramLeadDetails' stored procedure is used to get specific records from Program Lead table
SqlDataAdapter DAdapter = new SqlDataAdapter("uspGetProgramLeadDetails", Conn);
DAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
DAdapter.SelectCommand.Parameters.AddWithValue("@LoggedInUser", LoggedInUser);
DAdapter.SelectCommand.Parameters.AddWithValue("@RetMsg", returnmsg);
DAdapter.SelectCommand.Parameters.AddWithValue("@IsActive", IsActive);
DataSet DSet = new DataSet();
try
{
DAdapter.Fill(DSet, "ProgramLead");
return DSet.Tables["ProgramLead"];
}
catch
{
throw;
}
finally
{
DSet.Dispose();
DAdapter.Dispose();
Conn.Close();
Conn.Dispose();
}
}
/// <summary>
/// 下SQL Command取回資料
/// </summary>
/// <param name="sSqlCmd">SQL Commnad String</param>
/// <param name="sConnStr1">SQL Connection String</param>
/// <returns>回傳經由SQL Command Select後的結果，傳回DataTable</returns>
public DataTable execDataTable(string sSqlCmd, string sConnStr1)
{
SqlConnection conDB = new SqlConnection();
SqlDataAdapter sqlDataAdap = new SqlDataAdapter();
DataTable dttReturnData = new DataTable();
try
{
conDB = new SqlConnection(sConnStr1);
sqlDataAdap = new SqlDataAdapter(sSqlCmd, conDB);
sqlDataAdap.Fill(dttReturnData);
}
catch (Exception ex)
{
gLogger.ErrorException("DBConnection.execDataTable", ex);
throw ex;
}
finally
{
if (sqlDataAdap != null)
{
sqlDataAdap.Dispose();
}
if (conDB != null)
{
if (conDB.State == ConnectionState.Open)
{
conDB.Close();
}
conDB.Dispose();
}
}
return dttReturnData;
}
public static bool AppendDataTable(ref DataTable dsNewTable, string strSQL)
{
if (!OpenConnection())
{
return(false);
}
try
{
DataTable oDataSet = new DataTable();
System.Data.SqlClient.SqlDataAdapter DataAdapter;
DataAdapter = new System.Data.SqlClient.SqlDataAdapter(strSQL, lo_Connection);
System.Data.SqlClient.SqlCommandBuilder myDataRowsCommandBuilder = new System.Data.SqlClient.SqlCommandBuilder(DataAdapter);
DataAdapter.Update(dsNewTable);
DataAdapter.Dispose();
myDataRowsCommandBuilder.Dispose();
return(true);
}
catch
{
return(false);
}
finally
{
lo_Connection.Close();
}
}
public string getLocationInfoById(int LocationId)
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_locationInfoByCityId", sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@LocationId", SqlDbType.Int).Value = LocationId;
//try
//{
sda.Fill(ds);
dt = ds.Tables[0];
//}
//catch (Exception ex)
//{
//}
//finally
//{
sqlConn.Close();
sda.Dispose();
sqlConn.Dispose();
//}
return dt.Rows[0]["CountryName"].ToString() + " - " + dt.Rows[0]["CityName"].ToString();
}
public static bool CloneDataTable(ref DataTable ReturnTable, string TableName)
{
System.Data.SqlClient.SqlDataAdapter lo_Ada = new System.Data.SqlClient.SqlDataAdapter();
DataTable Return_DataTable = new DataTable();
if (!OpenConnection())
{
return(false);
}
try
{
System.Data.SqlClient.SqlCommand SqlCmd;
SqlCmd = new System.Data.SqlClient.SqlCommand("Select TOP 1 * from " + TableName, lo_Connection);
SqlCmd.Connection = lo_Connection;
lo_Ada.SelectCommand = SqlCmd;
lo_Ada.FillSchema(Return_DataTable, SchemaType.Source);
lo_Ada.Dispose();
lo_Ada = null;
ReturnTable = Return_DataTable;
return(true);
}
catch
{
return(false);
}
finally
{
lo_Connection.Close();
}
}
public Int32 getUserId(string VerificationCode)
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_loginSessionRead", sqlConn);
//try
//{
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@VerificationCode", SqlDbType.NVarChar).Value = VerificationCode;
sda.Fill(ds);
dt = ds.Tables[0];
//}
//catch (Exception ex)
//{
//}
//finally
//{
sqlConn.Close();
sda.Dispose();
sqlConn.Dispose();
//}
if (dt.Rows.Count == 0) //no user found
{
return 0;
}
else
{
return Convert.ToInt32(dt.Rows[0]["UserId"].ToString());
}
}
public System.Data.DataTable GetData(System.Data.SqlClient.SqlCommand cmd)
{
System.Data.DataTable dt = new System.Data.DataTable();
string strConnString = global::System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString;
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
con.ConnectionString = global::System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString;
System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
try
{
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
return(dt);
}
catch (Exception ex)
{
return(dt);
}
finally
{
con.Close();
sda.Dispose();
con.Dispose();
}
}
public async Task<List<WindowsDtls>> GetWindows()
{
var constring = @"Data Source='c:\users\u3696174\documents\visual studio 2012\Projects\CLSBforNode\CLSBforNode\App_Data\Database1.sdf'";
List<WindowsDtls> lst_result = new List<WindowsDtls>();
using (SqlConnection con=new SqlConnection(constring))
{
await con.OpenAsync();
using (SqlCommand cmd=new SqlCommand())
{
cmd.Connection = con;
cmd.CommandText = @"select * from Windows";
cmd.CommandType = CommandType.Text;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
adapter.Dispose();
con.Close();
WindowsDtls obj_single = null;
foreach (DataRow dr in dt.Rows)
{
obj_single = new WindowsDtls();
obj_single.ID = Convert.ToInt32( dr[0].ToString());
obj_single.Name = dr[1].ToString();
obj_single.Quantity = dr[2].ToString();
obj_single.Price = dr[3].ToString();
obj_single.Image = dr[4].ToString();
lst_result.Add(obj_single);
obj_single = null;
}
}
}
return lst_result;
}
public void BindGrid(GridView g, string query)
{
System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection();
cn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString;
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query);
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = cn;
try
{
cn.Open();
sda.SelectCommand = cmd;
//try
// {
sda.Fill(dt);
// }
//catch{}
g.DataSource = dt;
g.AllowPaging = true;
g.DataBind();
cn.Close();
cn.Dispose();
}
finally
{
cn.Close();
sda.Dispose();
cn.Dispose();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["kullanici"] != null || Session["yonetici"] != null)
{
}
else
{
Response.Redirect("KullaniciGiris.aspx");
}
try
{
Yardimci.baglanti.Open();
SqlCommand sinif_cek = new SqlCommand();
sinif_cek.CommandText = "SELECT SINIF_ID, SINIF_AD FROM SINIFLAR";
sinif_cek.Connection = Yardimci.baglanti;
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter(sinif_cek);
adp.Fill(ds, "SINIFLAR");
dwSiniflar.DataSource = ds.Tables["SINIFLAR"];
dwSiniflar.DataBind();
ds.Dispose();
adp.Dispose();
Yardimci.baglanti.Close();
}
catch (Exception hata)
{
Yardimci.baglanti.Close();
lbl_sonuc.Text = hata.Message;
}
}
public DataTable fetchAbuseIssueReport()
{
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
query = "issuesReportedAsAbuse";
dap = new SqlDataAdapter(query, con);
dap.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
dap.Fill(ds, "temp");
dap.Dispose();
return ds.Tables["temp"];
}
catch
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
private void txtPassword_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
{
if (Strings.Asc(e.KeyChar) == 13)
{
modMain.logsuccess = false;
modMain.sql = "select loginid,password from login";
modMain.comm = new System.Data.SqlClient.SqlCommand(modMain.sql, modMain.conn);
System.Data.SqlClient.SqlDataAdapter adapt = new System.Data.SqlClient.SqlDataAdapter(modMain.comm);
adapt.Fill(modMain.rslogin, "Login");
modMain.loguser = Strings.UCase(Strings.Trim(txtUser.Text));
modMain.logpass = Strings.Trim(txtPassword.Text);
DataRow objdatarow;
foreach (DataRow tempLoopVar_objdatarow in modMain.rslogin.Tables["Login"].Rows)
{
objdatarow = tempLoopVar_objdatarow;
if (Strings.UCase(System.Convert.ToString(objdatarow["loginid"])) == modMain.loguser)
{
if (modMain.decrypt_pass(System.Convert.ToString(objdatarow["password"])) == (modMain.logpass))
{
modMain.logsuccess = true;
adapt.Dispose();
adapt = null;
modMain.comm.Dispose();
modMain.comm = null;
frmMain frm = new frmMain();
this.Hide();
frm.Show();
return;
}
}
}
if (modMain.logsuccess == false)
{
Interaction.MsgBox("Login Failed ? Please Check whether your login details are true", 0, null);
txtUser.Focus();
}
adapt.Dispose();
adapt = null;
modMain.comm.Dispose();
modMain.comm = null;
}
}
public DataTable CreateDataTable(SqlCommand command)
{
DataTable dataTable = new DataTable();
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(command);
da.Fill(dataTable);
da.Dispose();
da = null;
return(dataTable);
}
public static bool FillDataTable_ViaCmd(ref DataTable ReturnTable, ref SqlCommand SqlCmd)
{
System.Data.SqlClient.SqlDataAdapter lo_Ada = new System.Data.SqlClient.SqlDataAdapter();
DataTable Return_DataTable = new DataTable();
SqlConnection ActiveConn;
if (!OpenConnection())
{
return(false);
}
else
{
ActiveConn = lo_Connection;
}
SqlCmd.Connection = ActiveConn;
SqlCmd.CommandTimeout = CommandTimeOutSeconds;
int Retry = 2;
while (Retry >= 0)
{
try
{
lo_Ada.SelectCommand = SqlCmd;
lo_Ada.Fill(Return_DataTable);
lo_Ada.Dispose();
lo_Ada = null;
ActiveConn.Close();
ReturnTable = Return_DataTable;
return(true);
}
catch (Exception ex)
{
if (Retry >= 1 && ex.Message.Contains("deadlock victim"))
{
System.Threading.Thread.Sleep(3337);
Retry -= 1;
}
else if (Retry >= 1 && (ex.Message.Contains("INSERT EXEC failed ") || ex.Message.Contains("Schema changed ")))
{
System.Threading.Thread.Sleep(3337);
Retry -= 1;
}
else
{
ActiveConn.Close();
Retry = -1;
}
}
}
return(false);
}
public DataRow GetSP_Row(string ProcedureName)
{
this.cmd.CommandText = ProcedureName;
this.cmd.CommandType = CommandType.StoredProcedure;//指定是存储过程
this.GenParameters();
System.Data.SqlClient.SqlDataAdapter ada = new System.Data.SqlClient.SqlDataAdapter();
ada.SelectCommand = (System.Data.SqlClient.SqlCommand)cmd;
DataTable dt = new DataTable();
ada.Fill(dt);
ada.Dispose();
return(dt.Rows.Count > 0 ? dt.Rows[0] : null);
}
private void PopulateDS()
{
ds.Clear();
SqlAdapter = new System.Data.SqlClient.SqlDataAdapter
("SELECT AmounBrickID,AmounBrickName FROM View_EmployeesBricks WHERE Deleted IS NULL AND EmployeeID = " + Session["EmployeeID"], myConn);
SqlAdapter.Fill(ds, "Bricks");
SqlAdapter = new System.Data.SqlClient.SqlDataAdapter
("SELECT RangeValue,RangeName FROM [AmounCRM2].[dbo].[NoOfPatients]", myConn);
SqlAdapter.Fill(ds, "NoOfPatients");
SqlAdapter = new System.Data.SqlClient.SqlDataAdapter
("SELECT ShortName,[RangeValue] FROM [AmounCRM2].[dbo].[PrescriptionHappit]", myConn);
SqlAdapter.Fill(ds, "PHabit");
SqlAdapter.Dispose();
}
private void FormBriefcaseFileNameInfo_Load(object sender, EventArgs e)
{
System.Data.SqlClient.SqlDataAdapter da = null;
try
{
da = Vetting.InspectionType.GetAdapter(ActiveConnection);
DataTable tbl = new DataTable();
da.Fill(tbl);
this.cmbVettingTypes.DataSource = tbl;
this.cmbVettingTypes.DisplayMember = "InspectionType";
}
finally
{
da.Dispose();
}
}
protected virtual void Dispose(bool disposing)
{
if (!this.disposedValue)
{
if (disposing)
{
}
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
Conn.Dispose();
Command.Dispose();
Adapt.Dispose();
Parametros.Clear();
Parametros = null;
Dt = null;
}
this.disposedValue = true;
}
public void Change(DataTable tablechange)
{
if (string.IsNullOrWhiteSpace(tablechange.TableName))
{
throw new Exception("ChangeSQL tablename error");
}
SqlDataReader read = null;
//SqlCommand cmd = null;
SqlDataAdapter adapter = null;
try
{
this.sqlMutex.WaitOne();
if (conn == null)
{
this.Open();
}
adapter = new System.Data.SqlClient.SqlDataAdapter();
adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("SELECT * FROM " + tablechange.TableName, this.conn);
System.Data.SqlClient.SqlCommandBuilder cmdBldr = new System.Data.SqlClient.SqlCommandBuilder(adapter);
adapter.UpdateCommand = cmdBldr.GetUpdateCommand();
//System.Data.SqlClient.SqlCommand cmd = cmdBldr.GetUpdateCommand();
//Console.WriteLine(cmd.ToString());
adapter.Update(tablechange);
}
finally
{
try { adapter.Dispose(); } catch { }
if (transac == null)
{
this.Close();
}
this.sqlMutex.ReleaseMutex();
}
}
private void txtsmanid_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
{
if (Strings.Asc(e.KeyChar) == 13)
{
bool user_correct;
user_correct = false;
modMain.sql = "select * from login";
modMain.comm = new System.Data.SqlClient.SqlCommand(modMain.sql, modMain.conn);
System.Data.SqlClient.SqlDataAdapter adapt = new System.Data.SqlClient.SqlDataAdapter(modMain.comm);
adapt.Fill(modMain.rslogin, "Login");
modMain.te1 = Strings.UCase(Strings.Trim(txtSmanid.Text));
DataRow objdatarow;
foreach (DataRow tempLoopVar_objdatarow in modMain.rslogin.Tables["Login"].Rows)
{
objdatarow = tempLoopVar_objdatarow;
if (Strings.UCase(System.Convert.ToString(objdatarow["salesmanid"])) == modMain.te1)
{
user_correct = true;
}
}
if (user_correct == false)
{
objdatarow = null;
modMain.comm.Dispose();
modMain.comm = null;
//rslogin.Dispose()
//rslogin = Nothing
adapt.Dispose();
adapt = null;
txtNewUser.Focus();
}
else
{
Interaction.MsgBox("SalesManid " + modMain.te1 + " already exists", 0, null);
txtSmanid.Focus();
}
user_correct = false;
}
modMain.te1 = null;
}
public static Boolean fillDataSet(ref DataSet dataSet, string dataTableName, string sCommandText)
{
System.Data.SqlClient.SqlConnection con = connect();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sCommandText, con);
System.Data.SqlClient.SqlDataAdapter adp = new System.Data.SqlClient.SqlDataAdapter(cmd);
dataSet.Tables.Add(new DataTable(dataTableName));
try
{
adp.Fill(dataSet.Tables[dataTableName]);
return(true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
adp.Dispose();
cmd.Dispose();
disconnect(con);
}
}
/// <summary>
/// Metodo de consulta de todos los viajes por estado
/// </summary>
/// <param name="soloactivos"></param>
/// <returns>Retorna un DataTable con todos los registros</returns>
public DataTable ConsultarViajes(bool soloactivos)
{
SqlCommand commandData = new SqlCommand();
System.Data.DataSet ds = new System.Data.DataSet();
DataTable dt = new DataTable();
OpenData("query");
string sqlconsulta = "SELECT [Id_viaje],c.Nombre,c.UserName, [Lugar_inicio], " +
"[Lugar_final],[Descripcion],[Tiempoestimado],[Estado]" +
"FROM[dbo].[Viaje] v with(nolock), Conductor c with(nolock)" +
"where v.Identificacion = c.Identificacion ";
if (soloactivos)
{
sqlconsulta += " and v.Estado='ACTIVO'";
}
commandData = new System.Data.SqlClient.SqlCommand(sqlconsulta, sqlConnData);
commandData.CommandType = CommandType.Text;
//agregamos el parametro al query con el valor requerido
SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(commandData);
try
{
da.Fill(dt);
}
catch (Exception)
{
ds = null;
}
finally
{
da.Dispose();
}
CloseData();
return(dt);
}
bool Completed; //did the translation complete?
public DataTranslator(DataSet ds, ImportTemplateSettings ImportSettings, List <String> tl, string gb)
{
Data2Translate = ds;
TemplateSettings = ImportSettings;
List2Translate = tl;
GrowerBlock = gb;
Completed = false; //Did tranlation complete? Not yet.
try
{
//get Commodity data from database
string connString = Properties.Settings.Default.ConnectionString;
string query = "select [Data_Column_Name],[Description],[Value], [Custom_Value] FROM Translation_Validation_Table"; // WHERE Famous_Validate = 1";
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn);
conn.Open();
DataSet commodityDataSet = new DataSet();
// create data adapter
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
// this will query the database and return the result to your datatable
da.Fill(commodityDataSet);
conn.Close();
da.Dispose();
CommodityTable = commodityDataSet.Tables[0]; //set the translation table
commodityDataSet.Dispose();
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to load the Commodity data for translation. \n" +
" Note what happened and contact administrator for help or see error log. \n");
Error_Logging el = new Error_Logging("There was an error while trying to load theCommodity data for translation. \n" + e);
ds.Dispose();
return;
}
}
public static DataSet fillDataSet(string sCommandText)
{
System.Data.SqlClient.SqlConnection con = connect();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sCommandText, con);
System.Data.SqlClient.SqlDataAdapter adp = new System.Data.SqlClient.SqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
adp.Fill(ds);
return(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
adp.Dispose();
cmd.Dispose();
disconnect(con);
}
}
private void txtUser_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
{
if (Strings.Asc(e.KeyChar) == 13)
{
bool user_correct;
user_correct = false;
modMain.sql = "select loginid,password from login";
modMain.comm = new System.Data.SqlClient.SqlCommand(modMain.sql, modMain.conn);
System.Data.SqlClient.SqlDataAdapter adapt = new System.Data.SqlClient.SqlDataAdapter(modMain.comm);
adapt.Fill(modMain.rslogin, "Login");
modMain.loguser = Strings.UCase(Strings.Trim(txtUser.Text));
DataRow objdatarow;
foreach (DataRow tempLoopVar_objdatarow in modMain.rslogin.Tables["Login"].Rows)
{
objdatarow = tempLoopVar_objdatarow;
if (Strings.UCase(System.Convert.ToString(objdatarow["loginid"])) == modMain.loguser)
{
user_correct = true;
}
}
if (user_correct == true)
{
objdatarow = null;
modMain.comm.Dispose();
modMain.comm = null;
//rslogin.Dispose()
//rslogin = Nothing
adapt.Dispose();
adapt = null;
txtPassword.Focus();
}
else
{
Interaction.MsgBox("Invalid User Name", 0, null);
}
}
}
public FamousXMLExporter(DataSet ds, ImportTemplateSettings ImS, List <String> dl)
{
Data2Export = ds.Copy();
exportStringList = dl;
ImportSettings = ImS;
try
{
//get Commodity data from database
string connString = Properties.Settings.Default.ConnectionString;
string query = "select [Commodity] FROM [StoneFuitCommodities]"; // List of stone fruits and berries;
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn);
conn.Open();
DataSet StoneFruitDataSet = new DataSet();
// create data adapter
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
// this will query the database and return the result to your datatable
da.Fill(StoneFruitDataSet);
conn.Close();
da.Dispose();
StoneFruitTable = StoneFruitDataSet.Tables[0]; //set the translation table
StoneFruitDataSet.Dispose();
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to load the Commodity data for XML export. \n" +
" Note what happened and contact administrator for help or see error log. \n");
Error_Logging el = new Error_Logging("There was an error while trying to load theCommodity data for XML export. \n" + e);
ds.Dispose();
return;
}
}
public CombineMixedBoxesOnPallets(DataSet ds, ImportTemplateSettings ts, string name)
{
WorkingDataSet = ds; //dataset frpm import spreadsheet
TemplateSettings = ts; //Import template settings
FormName = name;
try
{
//get Commodity data from database
string connString = Properties.Settings.Default.ConnectionString;
string query = "select [Data_Column_Name],[Description],[Value], [Custom_Value] FROM Translation_Validation_Table"; // WHERE Famous_Validate = 1";
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn);
conn.Open();
DataSet commodityDataSet = new DataSet();
// create data adapter
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
// this will query the database and return the result to your datatable
da.Fill(commodityDataSet);
conn.Close();
da.Dispose();
CommodityTable = commodityDataSet.Tables[0]; //set the translation table
commodityDataSet.Dispose();
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to load the Commodity data for the combining. \n" +
" Note what happened and contact administrator for help or see error log. \n");
Error_Logging el = new Error_Logging("There was an error while trying to load theCommodity data. \n" + e);
ds.Dispose();
return;
}
}
StringBuilder notTranslatedError = new StringBuilder(); //list to hold the name of fields that did not translate
public DataTranslatorByGrower(DataSet ds, ImportTemplateSettings ImportSettings, List <String> tl, string gi, string pf)
{
Data2Translate = ds;
TemplateSettings = ImportSettings;
Prefix = pf;
GrowerID = gi;
// Completed = false; //Did tranlation complete? Not yet.
if (Properties.Settings.Default.Mode == "Test")
{
GCVInfoTable = "GCV_Information_Test2";
TranslationDetailsTable = "Translation_Details_Test2";
}
else
{
GCVInfoTable = "GCV_Information2";
TranslationDetailsTable = "Translation_Details2";
}
/* Location in datarow of data to translate.
* Grower_ID - 0,
* Grower_Name - 1,
* Grower_Commodity_Code - 2,
* Commodity_Code - 3,
* Commodity - 4,
* Grower_Variety_Code - 5,
* Variety_Code - 6,
* Variety - 7,
* Stone_Fruit - 8,
* GCV_Code - 9,
* Grower_Style_Code - 10,
* Famous_Style_Code - 11,
* Grower_Size_Code - 12,
* Famous_Size_Code - 13,
* Grower_Pack_Code - 14,
* Famous_Pack_Code - 15,
* Adams_Pack_Code - 16,
* Grower_Label_Code - 17,
* Famous_Label_Code - 18,
* Adams_Label_Code - 19,
* Grower_Grade_Code - 20,
* Famous_Grade_Code - 21,
* Adams_Grade_Code - 22,
* Grower_Pallet_Type - 23,
* Famous_Pallet_Type - 24,
* Adams_Pallet_Type - 25
*
*/
try
{
//get Translation data from database
string connString = Properties.Settings.Default.ConnectionString;
string query =
"SELECT Grower_ID, Grower_Name, Grower_Commodity_Code, Commodity_Code, Commodity, Grower_Variety_Code, " +
"Variety_Code, Variety, Stone_Fruit, " + GCVInfoTable.ToString() + ".GCV_Code, Grower_Style_Code, " +
"Famous_Style_Code, Grower_Size_Code, Famous_Size_Code, Grower_Pack_Code, " +
"Famous_Pack_Code, Adams_Pack_Code, Grower_Label_Code, Famous_Label_Code, " +
"Adams_Label_Code, Grower_Grade_Code, Famous_Grade_Code, " +
"Adams_Grade_Code, Grower_Pallet_Type, Famous_Pallet_Type, " +
"Adams_Pallet_Type " +
"FROM " + GCVInfoTable.ToString() + " INNER JOIN " + TranslationDetailsTable.ToString() +
" ON " + GCVInfoTable.ToString() + ".GCV_Code = " + TranslationDetailsTable.ToString() + ".GCV_Code " +
"WHERE " + GCVInfoTable.ToString() + ".Grower_ID = " + GrowerID.ToString(); // Form translation table for grower;
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn);
conn.Open();
DataSet translationDataSet = new DataSet();
// create data adapter
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
// this will query the database and return the result to the datatable
da.Fill(translationDataSet);
conn.Close();
da.Dispose();
TranslationTable = translationDataSet.Tables[0]; //set the translation table
translationDataSet.Dispose();
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to create and load the translation data. \n" +
" Note what happened and contact administrator for help or see error log. \n");
Error_Logging el = new Error_Logging("There was an error while trying to create and load translation data. \n" + e);
ds.Dispose();
return;
}
}
protected void dxcbpodfiles_Callback(object sender, DevExpress.Web.ASPxClasses.CallbackEventArgsBase e)
{
try
{
string _folderpath = "";
//int _orderno = 0;
int _ordernocount = 0;
Int32 _folderid = 0;
bool _uploaded = false; //flagged if uploaded completed ok
_folderpath = return_selected_folder();
_folderid = return_selected_folderid(_folderpath);
_uploaded = this.dxhfmanager.Contains("upld") ? (bool)this.dxhfmanager.Get("upld") : false;
//09012011 stored in ilist to save casting datatable for IN query
//05012011 multiple order no's are stored in data table
//_ordernocount = _ol.Rows.Count;
//_orderno = wwi_func.vint(this.dxlblorderno1.Text.ToString()); //this.dxhfmanager.Contains("ord") ? wwi_func.vint(wwi_security.DecryptString(this.dxhfmanager.Get("ord").ToString(), "publiship")) : 0;
//DataTable _ol = (DataTable)Session["orderlist"];
IList <Int32> _ol = (IList <Int32>)Session["orderlist"];
_ordernocount = _ol.Count;
if (_uploaded & _folderid > 0 && _ordernocount > 0)
//if (_uploaded & _folderid > 0 && _orderno > 0)
{
//copy files from temporary folder
string _uid = wwi_security.DecryptString(this.dxhfmanager.Get("uid").ToString(), "publiship");
string _tempdir = DateTime.Now.ToShortDateString().Replace("/", "") + _uid;
string _sourcepath = System.IO.Path.Combine("~\\documents\\not found", _tempdir);
int _copies = file_transfer(_sourcepath, _folderpath, true);
if (_copies > 0)
{
//update order table
//save quote id to order table
//if (_ordernocount == 1)
//{
// //(DataRow)_ol.Rows[0][0]
// SubSonic.Update upd2 = new SubSonic.Update(DAL.Logistics.Schemas.OrderTable);
// recordsaffected = upd2.Set("document_folder").EqualTo(_folderid)
// .Where("OrderNumber").IsEqualTo(_ol[0])
// .Execute();
//}
//else
//{
//string _csv = wwi_func.datatable_to_string(_ol, ",");
string _csv = string.Join(",", _ol.Select(i => i.ToString()).ToArray());
//establish connection
ConnectionStringSettings _cs = ConfigurationManager.ConnectionStrings["PublishipSQLConnectionString"];
SqlConnection _cn = new SqlConnection(_cs.ConnectionString);
//create a sql data adapter
System.Data.SqlClient.SqlDataAdapter _adapter = new System.Data.SqlClient.SqlDataAdapter();
//instantiate event handler
_adapter.RowUpdated += new SqlRowUpdatedEventHandler(adapter_RowUpdated);
_adapter.SelectCommand = new SqlCommand("SELECT [document_folder], [orderNumber] FROM OrderTable WHERE [OrderNumber] IN (" + _csv + ")", _cn);
//populate datatable using selected order numbers
DataTable _dt = new DataTable();
_adapter.Fill(_dt);
//update document folder for each order number in _dt
for (int ix = 0; ix < _dt.Rows.Count; ix++)
{
_dt.Rows[ix]["document_folder"] = _folderid;
}
//update command
_adapter.UpdateCommand = new SqlCommand("UPDATE OrderTable SET [document_folder] = @document_folder WHERE [OrderNumber] = @OrderNumber;", _cn);
_adapter.UpdateCommand.Parameters.Add("@OrderNumber", SqlDbType.Int).SourceColumn = "OrderNumber";
_adapter.UpdateCommand.Parameters.Add("@document_folder", SqlDbType.Int).SourceColumn = "document_folder";
_adapter.UpdateBatchSize = 5; //_ordernocount;
_adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
_adapter.Update(_dt);
_adapter.Dispose();
_cn.Close();
//}
if (_countUpdated > 0) //countupdated will return the number of batches sent NOT the number of records updated
{
//disable session expiration which occurs when a directory is deleted
//uses System.Reflection
PropertyInfo p = typeof(System.Web.HttpRuntime).GetProperty("FileChangesMonitor", BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static);
object o = p.GetValue(null, null);
FieldInfo f = o.GetType().GetField("_dirMonSubdirs", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.IgnoreCase);
object monitor = f.GetValue(o);
MethodInfo m = monitor.GetType().GetMethod("StopMonitoring", BindingFlags.Instance | BindingFlags.NonPublic);
m.Invoke(monitor, new object[] { });
//kill temp folder
System.IO.Directory.Delete(Server.MapPath(_sourcepath), true);
this.dxlblinfo2.Text += _copies + " files have been uploaded to folder " + _folderid + " and linked to order numbers(s) " + _csv;
this.dxpnlinfo.Visible = true;
}
else
{
this.dxlblerr.Text = "We have not been able to link order number(s) to online folder " + _folderid + " please refer to Publiship I.T.";
this.dxpnlerr.Visible = true;
}
//end check records affected
}
}
//end update order table
}
catch (Exception ex)
{
this.dxlblerr.Text = ex.Message.ToString();
this.dxpnlerr.Visible = true;
}
}
public bool Validate() //Validation routine
{
DataRow[] FoundValidateRows;
DataRow[] CommodityValidateRows;
Passed = false;
int rownumber = 0;
string sTempField;
DataTable DatTable = new DataTable();
DatTable = Data2Validate.Tables[0];
DataSet ds = new DataSet();
try
{
//get Validation data from database
string connString = Properties.Settings.Default.ConnectionString;
string query = "select [Data_Column_Name],[Description],[Value], [Custom_Value], [Famous_Validate], [Ignore] from Translation_Validation_Table"; // WHERE Famous_Validate = 1";
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn);
conn.Open();
// create data adapter
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
// this will query the database and return the result to your datatable
da.Fill(ds);
conn.Close();
da.Dispose();
ValidationTable = ds.Tables[0]; //set the translation table
ds.Dispose();
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to load the Validation table. \n" +
" Note what happened and contact administrator for help or see error log. \n");
Error_Logging el = new Error_Logging("There was an error while trying to load the Validation Table. \n" + e);
ds.Dispose();
return(false);
}
try
{
//Validate values generic routine *************************************************************************
//Takes the list of fields to evaluate and iterates through each of them.
foreach (string sField in List2Validate)
{
rownumber = 0;
//This is for the case where the size and style are contatonated into one field in the data streadsheet. ***
if ((TemplateSettings.SizeColumn == TemplateSettings.StyleColumn) & ((sField == "Size") || (sField == "Style")))
{
sTempField = "PackCode";
}
else
{
sTempField = sField;
}
//*** ***
FoundValidateRows = ValidationTable.Select("Data_Column_Name = " + "'" + sTempField + "'"); //get all rows for sField from validation table
CommodityValidateRows = ValidationTable.Select("Data_Column_Name = 'Commodity'"); //get all rows for Commodity from validation table
foreach (DataRow row in Data2Validate.Tables[0].Rows) //interate through data rows and check the sField column
{
Passed = false;
foreach (DataRow validaterow in FoundValidateRows) //FoundValidateRows contains all the validation rows for sField
{
if (row[TemplateSettings.DataColumnLocation(sField).Column].ToString().Trim() == validaterow[ValidateWhat].ToString().Trim() &&
validaterow[4].ToString() == "1") //Validate what tells
//it to check value or description in validate table
{
Passed = true; //If found, set passed to true
}
else if (sField == "Grade") //special case for grade. Stone Fruit has no grade and should not be validated
{
foreach (DataRow Commodityrow in CommodityValidateRows)
{
if (row[TemplateSettings.CommodityColumn].ToString().Trim() == Commodityrow[2].ToString() && Commodityrow[3].ToString() == "Stone Fruit")
{
Passed = true; //If stone fruit and Grade, set passed to true as grade is not validated
}
}
}
}
if (!Passed) //if it failed the validation add the location in the spreadsheet to the invalid list
{
if (rownumber >= TemplateSettings.DataColumnLocation(sField).Row) //The sField row parm is the start row.
{
if (!((TemplateSettings.SizeColumn == TemplateSettings.StyleColumn) & (sField == "Style"))) //Skip for second cycle for case of Style_Size field
{ // data as it will cycle for both size and style.
InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.DataColumnLocation(sField).Column)); //add location of all failures to list.
}
}
}
if (InvalidItemList.Count() > 0) //if there were any invalid entries, it did not pass
{
Passed = false;
}
rownumber++;
}
}
//Check for missing pack code, fumigation, hatch, deck values and duplicate Tag Numbers.
rownumber = 0;
foreach (DataRow row in Data2Validate.Tables[0].Rows) //interate through data rows and validate other fields
{
Passed = false;
if (String.IsNullOrEmpty(row[TemplateSettings.PackCodeColumn].ToString())) //check for empty pack codes
{
InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.PackCodeColumn)); //add location of failures to list.
}
if (String.IsNullOrEmpty(row[TemplateSettings.HatchColumn].ToString())) //check for empty hatch
{
InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.HatchColumn)); //add location of failures to list.
row[TemplateSettings.HatchColumn] = " ";
}
if (String.IsNullOrEmpty(row[TemplateSettings.DeckColumn].ToString())) //check for empty Deck
{
InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.DeckColumn)); //add location of failures to list.
row[TemplateSettings.DeckColumn] = " ";
}
if (row[TemplateSettings.TagNumberColumn].ToString().Trim().Length > 12 || row[TemplateSettings.TagNumberColumn].ToString().Trim().Length < 1) //check for too long too short of tag numbers.
{
InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.TagNumberColumn)); //add location of failures to list.
}
if (InvalidItemList.Count() > 0) //if there were any invalid entries, it did not pass
{
Passed = false;
}
if (String.IsNullOrEmpty(row[TemplateSettings.FumigatedColumn].ToString())) //check for empty fumigation
{
InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.FumigatedColumn)); //add location of failures to list.
}
else
if (!(row[TemplateSettings.FumigatedColumn].ToString() == "1" || row[TemplateSettings.FumigatedColumn].ToString() == "0"))
{
InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.FumigatedColumn)); //add location of failures to list.
}
//check Tag Numbers
if (TagNumberExist.TagNumberExist(row[TemplateSettings.TagNumberColumn].ToString().Trim()))
{
InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.TagNumberColumn));
}
rownumber++;
}
}
catch (Exception e)
{
MessageBox.Show("Data Validation process had an error. Please note what was done and see administrator for help or see error log. \n");
Error_Logging el = new Error_Logging("Data Validation process had an error. \n" + e);
return(false);
}
if (InvalidItemList.Count() > 0) //if there were any invalid entries, it did not pass
{
Passed = false;
}
else
{
Passed = true;
}
return(Passed);
} //end of Validate Method
