private void btnOk_Click(object sender, EventArgs e)
{
string sql = "select * from [staff] where [scode]=@scode and [password]=@password";
OleDbCommand cmd = new OleDbCommand(sql, DataAccess.getInstance().getDataConnection());
cmd.Parameters.AddWithValue("scode", ((StaffModel) cboUsername.SelectedValue).Value);
cmd.Parameters.AddWithValue("password", txtPassword.Text);
OleDbDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if(!rdr.GetBoolean(rdr.GetOrdinal("isactive")))
{
MessageBox.Show("Your account has been deactivated. Please consult your administration regarding this");
rdr.Close();
cmd.Dispose();
return;
}
Program.loginStaff = new StaffModel(rdr.GetInt32(rdr.GetOrdinal("staffno")), rdr.GetString(rdr.GetOrdinal("scode")), rdr.GetString(rdr.GetOrdinal("sname")), "");
rdr.Close();
cmd.Dispose();
graceClose = true;
Close();
}
else
{
MessageBox.Show("You have supply a wrong credential", Application.ProductName);
return;
}
}
public bool IsValidModerator(string login, string password)
{
var conn =
new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|Użytkownicy.accdb");
var cmd = new OleDbCommand("SELECT [Login] FROM [Users] " + @"WHERE [Login] = @l AND [Hasło] = @p AND [Prawa] = @a") { Connection = conn };
cmd.Parameters.Clear();
cmd.Parameters.Add(new OleDbParameter("@l", OleDbType.VarWChar)).Value = login;
cmd.Parameters.Add(new OleDbParameter("@p", OleDbType.VarWChar)).Value = password;
cmd.Parameters.Add(new OleDbParameter("@a", OleDbType.VarWChar)).Value = "Moderator";
conn.Open();
var reader = cmd.ExecuteReader();
if (reader != null && reader.HasRows)
{
reader.Dispose();
cmd.Dispose();
return true;
}
else
{
reader?.Dispose();
cmd.Dispose();
return false;
}
}
private void btnNew_Click( object sender, EventArgs e )
{
if( Program.UserType == UserType.Customer )
return;
AddDlg dlg = new AddDlg();
if( dlg.ShowDialog( this ) != DialogResult.OK )
return;
RefillComboBoxes();
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "select top 1 * from WedDress order by [ID] desc";
cmd.Connection = Program.Database;
cmd.CommandType = CommandType.Text;
OleDbDataAdapter oda = new OleDbDataAdapter( cmd);
DataTable dt = new DataTable();
oda.Fill( dt );
dsDress.Tables["WedDress"].Merge( dt );
dt.Dispose();
oda.Dispose();
cmd.Dispose();
}
private void btnDel_Click( object sender, EventArgs e )
{
if( Program.UserType != UserType.Admin )
return;
if( dataGridView.CurrentRow == null )
return;
int id = (int)dataGridView.CurrentRow.Cells[0].Value;
DataTable dt = dsDress.Tables["WedDress"];
DataRow[] rows = dt.Select( "[ID]=" + id );
if( rows.GetLength( 0 ) <= 0 )
return;
if( MessageBox.Show( this, "��ȷʵҪɾ��ѡ���ķ�װ��?", Program.AppTitle,
MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2 )
== DialogResult.No )
return;
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "delete from [WedDress] where [ID]=" + id.ToString();
cmd.CommandType = CommandType.Text;
cmd.Connection = Program.Database;
cmd.ExecuteNonQuery();
cmd.Dispose();
dt.Rows.Remove( rows[0] );
}
public static int ExecuteSql(string SQLString)
{
int num2;
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand command = new OleDbCommand(SQLString, connection);
try
{
connection.Open();
num2 = command.ExecuteNonQuery();
}
catch (OleDbException exception)
{
connection.Close();
throw new Exception(exception.Message);
}
finally
{
if (command != null)
{
command.Dispose();
}
}
}
return num2;
}
public NewRentDlg( int nDressId, DateTime date )
{
m_nDressId = nDressId;
m_date = date;
m_bModify = true;
InitializeComponent();
OleDbCommand oc = new OleDbCommand();
oc.CommandText = "select startdate, enddate, customer from rent where dressid=? and ? between startdate and enddate";
oc.CommandType = CommandType.Text;
oc.Connection = Program.Database;
oc.Parameters.Add( "a", OleDbType.Integer ).Value = m_nDressId;
oc.Parameters.Add( "b", OleDbType.Date ).Value = date;
OleDbDataReader reader = oc.ExecuteReader();
reader.Read();
dtStartDate.Value = reader.GetDateTime( 0 );
dtEndDate.Value = reader.GetDateTime( 1 );
txtCustomer.Text = reader.GetString( 2 );
reader.Dispose();
oc.Dispose();
this.Text = "�ij�����Ϣ";
}
/// <summary>
/// kjhkdfhgkjh
/// </summary>
/// <param name="sSQL"></param>
/// <param name="conecOledb"></param>
/// <returns> Ele retorna algo de bom</returns>
protected static OleDbDataReader cria_DataReader_OleDb(String sSQL, OleDbConnection conecOledb)
{
OleDbCommand comando = new OleDbCommand(sSQL, conecOledb);
OleDbDataReader dr = comando.ExecuteReader(CommandBehavior.CloseConnection);
comando.Dispose();
return dr;
}
public String[] GetFirstnames()
{
List<string> items = new List<string>();
string strSQL = "";
OleDbDataReader objReader = null;
OleDbConnection objConn = null;
OleDbCommand objCommand = null;
strSQL = "SELECT firstname from Employees ORDER BY firstname";
objConn = new OleDbConnection(GetconnectstringLocal());
objConn.Open();
objCommand = new OleDbCommand(strSQL, objConn);
objReader = objCommand.ExecuteReader();
while (objReader.Read())
{
items.Add(objReader["firstname"].ToString());
}
objReader.Close(); objReader.Dispose(); objReader = null;
objCommand.Dispose(); objCommand = null;
objConn.Close(); objConn.Dispose(); objConn = null;
return items.ToArray();
}
public static bool delete_tis_color(String p_id)
{
try
{
String strQuery = String.Empty;
OleDbCommand sqlComm = new OleDbCommand();
//
strQuery = String.Empty;
strQuery += "DELETE FROM [tis_color]";
strQuery += " WHERE [color_id] = '" + p_id + "'";
//
connect_db();
sqlComm.Connection = sql_conn;
sqlComm.CommandText = strQuery.ToString();
sqlComm.ExecuteNonQuery();
sqlComm.Dispose();
disconnect_db();
//
return true;
}
catch (Exception e)
{
PCMSG.ShowError(e);
return false;
}
}
/// <summary>
/// 读取Excel文件的表头 指定Sheet
/// </summary>
/// <param name="filepath">文件路径</param>
/// <returns>DataTable</returns>
public static DataTable ReadExcelHeader(string fileName, string sheet)
{
System.Data.DataSet itemDS = new DataSet();
if (fileName.Trim().ToUpper().EndsWith("XLS") || fileName.Trim().ToUpper().EndsWith("XLSX"))
{
string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HdR=YES;\"";
System.Data.OleDb.OleDbConnection conn = null;
System.Data.OleDb.OleDbCommand oledbCommd = null;
try
{
conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = connStr;
conn.Open();
string sqlText = "select top 1 * from [" + sheet + "]";
oledbCommd = new System.Data.OleDb.OleDbCommand(sqlText, conn);
oledbCommd.CommandTimeout = 100000;
//执行
System.Data.OleDb.OleDbDataAdapter oledbDA = new System.Data.OleDb.OleDbDataAdapter(oledbCommd);
oledbDA.Fill(itemDS);
}
catch
{ }
finally
{
//释放
oledbCommd.Dispose();
conn.Close();
}
//创建连接
}
return(itemDS.Tables[0]);
}
private void AttributesForm_Load(object sender, EventArgs e)
{
try
{
OleDbCommand command = new OleDbCommand(srcCommand, Con);
this.attributeSchemaXml = Convert.ToString(command.ExecuteScalar());
command.Dispose();
this.attributeSet = new DataSet("AttributesSet");
if ((this.attributeSchemaXml == null) || (this.attributeSchemaXml.Length < 1))
{
this.attributesTable = this.attributeSet.Tables.Add("AttributesTable");
this.attributesTable.Columns.Add("Attribute Name");
this.attributesTable.Columns.Add("Attribute Value");
DataRow row = this.attributesTable.NewRow();
row["Attribute Name"] = "Enter name Of attribute";
row["Attribute Value"] = "Enter attribute value";
this.attributesTable.Rows.Add(row);
this.AttributesGrid.SetDataBinding(this.attributeSet, this.attributeSet.Tables[0].TableName);
}
else
{
this.attributeSet.ReadXml(new XmlTextReader(new StringReader(this.attributeSchemaXml)));
this.attributeSchemaXml = null;
this.AttributesGrid.SetDataBinding(this.attributeSet, this.attributeSet.Tables[0].TableName);
}
}
catch (Exception exception)
{
BusinessLogic.MyMessageBox(exception.Message);
}
}
public DataTable sepetcevir(string uyeID, string map)
{
OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + map);
cnn.Open();
OleDbCommand cmd = new OleDbCommand("select sepet from uyeler where uyeid=" + uyeID, cnn);
OleDbDataReader rdr = cmd.ExecuteReader();
rdr.Read();
string[] urunler = rdr[0].ToString().Split(',');
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("fid"), new DataColumn("aciklama"), new DataColumn("fiyat") });
if (rdr[0].ToString() != "")
{
foreach (string item in urunler)
{
cmd.Dispose();
string fid = item;
cmd = new OleDbCommand("select * from fotolar where fot_id=" + fid, cnn);
OleDbDataReader rdr2 = cmd.ExecuteReader();
rdr2.Read();
string ack = rdr2[5].ToString();
string fiyat = rdr2[6].ToString();
dt.Rows.Add(fid, ack, fiyat);
}
}
cnn.Close();
return dt;
}
public String[] SutunAdlari(String cmdSorgu)
{
String[] dizi = null;
try
{
Kontrol();
cmd = new OleDbCommand(cmdSorgu, bglnti);
dR = cmd.ExecuteReader();
dizi = new String[dR.FieldCount];
for (int i = 0; i < dR.FieldCount; i++)
{
dizi[i] = dR.GetName(i);
}
dR.Close();
dR.Dispose();
cmd.Dispose();
}
catch (OleDbException ole)
{
MessageBox.Show(ole.Message);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally { bglnti.Close(); }
return(dizi);
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, string content)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand(SQLString, connection);
System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter("@content", OleDbType.VarChar);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.OleDb.OleDbException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
///执行Sql语句
/// </summary>
/// <param name="str_Sql">要执行的Sql语句</param>
public string ExeSql(string str_Sql)
{
string errorstring = Open();
if (errorstring != "OK")
{
return(errorstring);
}
//myCommand = new SqlCommand(str_Sql,myConnection);
myCommand = new System.Data.OleDb.OleDbCommand(str_Sql, myConnection);
try
{
myCommand.ExecuteNonQuery();
}
catch (SqlException e)
{
string errorMessage = e.Message;
return(errorMessage);
}
finally
{
myCommand.Dispose();
}
return("OK");
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public override DataSet ExecuteDataSet(string SQLString, params IDataParameter[] cmdParms)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (OleDbException e)
{
//LogManage.OutputErrLog(e, new Object[] { SQLString, cmdParms });
throw;
}
finally
{
cmd.Dispose();
connection.Close();
}
return ds;
}
}
}
public static bool DownLoadXml(string fileID, string filePath)
{
bool ret = true;
string errMsg="";
try
{
OleDbConnection db2conn = new OleDbConnection(DBdb2.SetConString());
string sqlstr = "select * from T_SYS_MENU where T_XMLID='" + fileID + "'";
OleDbCommand db2cmd = new OleDbCommand(sqlstr, db2conn);
db2conn.Open();
OleDbDataReader db2reader = db2cmd.ExecuteReader();
string FileName = filePath;
if (!db2reader.Read())
{
FileName = "";
}
else
{
byte[] bytes = (byte[])db2reader["B_XML"];
FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);
fs.Write(bytes, 0, bytes.Length);
fs.Flush();
fs.Close();
}
db2reader.Close();
db2cmd.Dispose();
db2conn.Close();
}
catch (Exception ce)
{
errMsg = ce.Message;
ret = false;
}
return ret;
}
//Retourne les stats
public static Statistique getSats(int noJoueur)
{
Statistique stat = null;
OleDbConnection connexion = new OleDbConnection(connBD);
try
{
connexion.Open();
commande = new OleDbCommand("SELECT * FROM tblStatistique WHERE noJoueur=@noJoueur", connexion);
commande.Parameters.Add("@noJoueur", OleDbType.Integer).Value = noJoueur;
OleDbDataReader reader = commande.ExecuteReader();
while (reader.Read())
{
int nbGagne = reader["nbPartieGagne"] == DBNull.Value ? 0 : Convert.ToInt32(reader["nbPartieGagne"]);
int nbPerdu = reader["nbPartiePerdu"] == DBNull.Value ? 0 : Convert.ToInt32(reader["nbPartiePerdu"]);
int score = reader["score"] == DBNull.Value ? 0 : Convert.ToInt32(reader["score"]);
stat = new Statistique(nbGagne, nbPerdu, score);
}
return stat;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
commande.Dispose();
connexion.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
if (Session["oturumid"] == null)
Response.Redirect("giris.aspx");
else if (Session["oturumdurum"].ToString() == "1")
Response.Redirect("adminpaneli.aspx");
else
{
string fid = (sender as Button).CommandArgument;
string kid = Session["oturumid"].ToString();
string sepet = "";
OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + Server.MapPath("App_Data/database.mdb"));
cnn.Open();
OleDbCommand cmd = new OleDbCommand("select sepet from uyeler where uyeid=" + kid, cnn);
OleDbDataReader rdr = cmd.ExecuteReader();
rdr.Read();
sepet = rdr[0].ToString();
if (sepet == "")
sepet = fid;
else
sepet += "," + fid;
cmd.Dispose();
cmd = new OleDbCommand("update uyeler set sepet='" + sepet + "' where uyeid=" + kid, cnn);
cmd.ExecuteNonQuery();
cnn.Close();
Session.Add("um", "Fotoğraf sepetinize eklendi. İyi alışverişler.");
Response.Redirect("uyepaneli.aspx");
}
}
protected void btnInstall_Click(object sender, EventArgs e)
{
string ConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\{0}.mdb;",
txtDatabaseName.Text);
OleDbConnection OConn = new OleDbConnection(ConnectionString);
StreamReader Sr = new StreamReader(Server.MapPath("~/Setup/Scripts/Access.sql"));
try
{
File.Copy(Server.MapPath("~/Setup/Scripts/Blogsa.mdb"), Server.MapPath(string.Format("~/App_Data/{0}.mdb", txtDatabaseName.Text)));
//Update WebSite Url
string strUrl = Request.Url.AbsoluteUri.Substring(0
, Request.Url.AbsoluteUri.IndexOf(Request.Url.AbsolutePath) + (Request.ApplicationPath.Equals("/") ? 0 : Request.ApplicationPath.Length)) + "/";
OConn.Open();
while (!Sr.EndOfStream)
{
//Create DB
string Commands = Sr.ReadLine().ToString();
if (!Commands.StartsWith("/*"))
{
OleDbCommand OComm = new OleDbCommand(Commands, OConn);
OComm.ExecuteNonQuery();
OComm.Dispose();
}
}
Sr.Close();
string strLang = (string)Session["lang"];
string strRedirectPage = String.Format("Completed.aspx?Setup={0}&lang={1}", BSHelper.SaveWebConfig(ConnectionString, "System.Data.OleDb"), strLang);
Response.Redirect(strRedirectPage, false);
}
catch (Exception ex)
{
BSLog l = new BSLog();
l.CreateDate = DateTime.Now;
l.LogType = BSLogType.Error;
l.LogID = Guid.NewGuid();
l.RawUrl = Request.RawUrl;
l.Source = ex.Source;
l.StackTrace = ex.StackTrace;
l.TargetSite = ex.TargetSite;
l.Url = Request.Url.ToString();
l.Save();
divError.Visible = true;
lblError.Text = ex.Message;
if (OConn.State == ConnectionState.Open)
{
OConn.Close();
}
File.Delete(Server.MapPath("~/App_Data/" + txtDatabaseName.Text));
}
finally
{
if (OConn.State == ConnectionState.Open)
OConn.Close();
Sr.Close();
}
}
/// <summary>
/// Executes an inline SQL statement and returns a data table.
/// </summary>
/// <param name="dbStatement">Inline SQL</param>
/// <param name="connectionString">Connection string</param>
/// <returns>Data table containing the return data</returns>
public static DataTable RunQuery(string dbStatement, string connectionString)
{
OleDbConnection dbConnection = null;
OleDbCommand dbCommand = null;
OleDbDataAdapter adapter = null;
DataTable dt = null;
try
{
dbConnection = new OleDbConnection(connectionString);
dbCommand = new OleDbCommand(dbStatement, dbConnection);
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandTimeout = 600;
adapter = new OleDbDataAdapter(dbCommand);
dt = new DataTable();
dbConnection.Open();
adapter.Fill(dt);
return dt;
}
finally
{
if (adapter != null)
adapter.Dispose();
if (dbCommand != null)
dbCommand.Dispose();
if (dbConnection != null)
dbConnection.Dispose();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
Label6.Visible = false;
OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + Server.MapPath("App_Data/database.mdb"));
cnn.Open();
OleDbCommand cmd = new OleDbCommand("select * from uyeler where uyeadi='" + TextBox1.Text + "'",cnn);
OleDbDataReader rdr = cmd.ExecuteReader();
if(rdr.Read()==true)
{
Label6.Visible = true;
Label6.Text = "Bu kullanıcı adı zaten alınmış.";
}
else
{
cmd.Dispose();
string sorgu = "insert into uyeler(uyeadi,parola,adsoyad,mail,durum) values('" + TextBox1.Text + "', '" + TextBox4.Text + "', '" + TextBox2.Text + "', '" + TextBox3.Text + "', '0')";
cmd = new OleDbCommand(sorgu, cnn);
cmd.ExecuteNonQuery();
cnn.Close();
Server.Transfer("yenikayit.aspx");
}
cnn.Close();
}
private void chkComplete_CheckedChanged(object sender, EventArgs e)
{
string sql = "update bulletin set [complete]=true where [seqno]=@seqno";
OleDbCommand cmd = new OleDbCommand(sql, DataAccess.getInstance().getDataConnection());
cmd.Parameters.AddWithValue("seqno", Program.listBulletin[iPosMessage].seqNo);
cmd.ExecuteNonQuery();
cmd.Dispose();
//check it
sql = "select [complete] from bulletin where seqno=@seqno";
cmd = new OleDbCommand(sql, DataAccess.getInstance().getDataConnection());
cmd.Parameters.AddWithValue("seqno", Program.listBulletin[iPosMessage].seqNo);
OleDbDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (rdr.GetBoolean(rdr.GetOrdinal("complete")))
{
chkComplete.Checked = true;
Program.listBulletin[iPosMessage].complete = true;
}
else
{
MessageBox.Show("Unable to mark this task as complete! Please consult your system administration", Application.ProductName);
}
}
rdr.Close();
cmd.Dispose();
}
protected void btnlogout_Click(object sender, EventArgs e)
{
conlogout = i.GetOledbDbConnection();
cmdlogout = i.GetOledbDbCommand();
try
{
conlogout = DBConnection.GetConnection();
cmdlogout.Connection = conlogout;
cmdlogout.CommandType = CommandType.StoredProcedure;
cmdlogout.CommandText = "Logout";
cmdlogout.Parameters.AddWithValue("@USERNAME",s);
int result = cmdlogout.ExecuteNonQuery();
if (result == 1)
{
// Response.Write("logout successfull");
Response.Redirect("Login.aspx");
Session.Clear();
}
else
Response.Write("logout unsuccessfull");
}
catch (Exception ex)
{
Response.Write("logout unsuccessfull");
}
finally
{
cmdlogout.Dispose();
conlogout.Dispose();
}
}
protected void Page_Load(object sender, System.EventArgs e)
{
// resolve the address to the Access database
string fileNameString = this.MapPath(".");
fileNameString += @"..\..\..\..\data\chartdata.mdb";
// initialize a connection string
string myConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileNameString;
// define the database query
string mySelectQuery="SELECT * FROM REPS;";
// create a database connection object using the connection string
OleDbConnection myConnection = new OleDbConnection(myConnectionString);
// create a database command on the connection using query
OleDbCommand myCommand = new OleDbCommand(mySelectQuery, myConnection);
myConnection.Open();
// set chart data source - the data source must implement IEnumerable
Chart1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
// set series members names for the X and Y values
Chart1.Series["Series 1"].XValueMember = "Name";
Chart1.Series["Series 1"].YValueMembers = "Sales";
// data bind to the selected data source
Chart1.DataBind();
myCommand.Dispose();
myConnection.Close();
}
public static DataTable GetData(string strConn, string strSql)
{
DataTable dt = new DataTable("td");
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
OleDbCommand cmd = null;
OleDbDataAdapter da = null;
try
{
cmd = new OleDbCommand(strSql, conn);
da = new OleDbDataAdapter { SelectCommand = cmd };
da.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw new Exception("error getting data " + ex.Message);
}
finally
{
if (da != null) { da.Dispose(); }
if (cmd != null) { cmd.Dispose(); }
conn.Close();
}
}
}
public int DeleteInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)
{
try
{
OleDbConnection conn = null;
OleDbCommand cmd = null;
try
{
conn = new OleDbConnection(SqlHelper.ConnString);
conn.Open();
cmd = new OleDbCommand(GenerateQuery(true, authenticationOption), conn);
cmd.CommandTimeout = CommandTimeout;
cmd.Parameters.Add(CreateInputParam("@InactiveSinceDate", OleDbType.VarChar, userInactiveSinceDate.ToUniversalTime()));
return cmd.ExecuteNonQuery();
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (conn != null)
{
conn.Close();
conn = null;
}
}
}
catch
{
throw;
}
}
/// <summary>
/// Execute Insert,Update
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdParams"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string strSql, params OleDbParameter[] cmdParams)
{
OleDbConnection sqlCon = OleDbConnect();
OleDbCommand sqlCmd = new OleDbCommand(strSql, sqlCon);
sqlCmd.CommandType = CommandType.Text;
//OleDbTransaction trans = sqlCon.BeginTransaction();
//sqlCmd.Transaction = trans;
if (cmdParams != null)
{
foreach (OleDbParameter parm in cmdParams)
{
sqlCmd.Parameters.Add(parm);
}
}
try
{
sqlCon.Open();
int num = sqlCmd.ExecuteNonQuery();
//trans.Commit();
return num;
}
catch
{
//trans.Rollback();
return 0;
}
finally
{
sqlCmd.Dispose();
sqlCon.Close();
sqlCon.Dispose();
}
}
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
int result;
using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
{
System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(strSQL, oleDbConnection);
System.Data.OleDb.OleDbParameter oleDbParameter = new System.Data.OleDb.OleDbParameter("@fs", System.Data.OleDb.OleDbType.Binary);
oleDbParameter.Value = fs;
oleDbCommand.Parameters.Add(oleDbParameter);
try
{
oleDbConnection.Open();
int num = oleDbCommand.ExecuteNonQuery();
result = num;
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception(ex.Message);
}
finally
{
oleDbCommand.Dispose();
oleDbConnection.Close();
}
}
return(result);
}
public static int ExecuteSql(string SQLString, string content)
{
int result;
using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
{
System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(SQLString, oleDbConnection);
System.Data.OleDb.OleDbParameter oleDbParameter = new System.Data.OleDb.OleDbParameter("@content", System.Data.OleDb.OleDbType.VarChar);
oleDbParameter.Value = content;
oleDbCommand.Parameters.Add(oleDbParameter);
try
{
oleDbConnection.Open();
int num = oleDbCommand.ExecuteNonQuery();
result = num;
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception(ex.Message);
}
finally
{
oleDbCommand.Dispose();
oleDbConnection.Close();
}
}
return(result);
}
public static OleDbDataReader DataReader(OleDbConnection conn, string sqlText, OleDbParameter[] parms)
{
OleDbCommand sqlCommand = new OleDbCommand();
PrepareCommand(conn, sqlCommand, sqlText, parms);
OleDbDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
sqlCommand.Dispose();
return reader;
}
//הפעולה מקבלת שם מסד נתונים ומחרוזת מחיקה/ הוספה/ עדכון
//ומבצעת את הפקודה על המסד הפיזי
public static void DoQuery(string sqlQuery)
{
OleDbConnection conn = ConnectToDb();
conn.Open();//פתיחת חיבור למסד הנתונים
OleDbCommand com = new OleDbCommand(sqlQuery, conn);//הגדרת אובייקט commend לטיפול בפעולת sql לעדכון המסד
com.ExecuteNonQuery();
com.Dispose();
conn.Close();//סגירת חיבור
}
//הפעולה מקבלת שם מסד נתונים ומחרוזת מחיקה/ הוספה/ עדכון
//ומבצעת את הפקודה על המסד הפיזי
/// <summary>
/// To Execute update / insert / delete queries
/// הפעולה מקבלת שם קובץ ומשפט לביצוע ומבצעת את הפעולה על המסד
/// </summary>
public static void DoQuery(string fileName, string sql)
{
OleDbConnection conn = ConnectToDb(fileName);
conn.Open();
OleDbCommand com = new OleDbCommand(sql, conn);
com.ExecuteNonQuery();
com.Dispose();
conn.Close();
}
static void Main(string[] args)
{
try
{
FileStream fs = new FileStream("connstr.txt", FileMode.Open);
byte[] connstr = new byte[fs.Length];
fs.Read(connstr, 0, (int)fs.Length);
fs.Close();
StringBuilder sb = new StringBuilder();
for(int i = 0; i < connstr.Length; i++)
sb.Append((char)connstr[i]);
connStrMdb = sb.ToString().Split('\n')[0].Replace("\r","");
connStrMSSql = sb.ToString().Split('\n')[1].Replace("\r", "");
accessConn = new OleDbConnection(connStrMdb);
msSqlConn = new SqlConnection(connStrMSSql);
accessConn.Open();
msSqlConn.Open();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.ReadKey();
return;
}
OleDbCommand accessCmd = new OleDbCommand();
accessCmd.Connection = accessConn;
SqlCommand msSqlCmd = new SqlCommand();
msSqlCmd.Connection = msSqlConn;
msSqlCmd2.Connection = msSqlConn;
msSqlCmd2.CommandText = "delete FPObject";
msSqlCmd2.ExecuteNonQuery();
msSqlCmd2.CommandText = "delete Sequences";
msSqlCmd2.ExecuteNonQuery();
migrateCustomer(accessCmd, msSqlCmd);
migratePrint_Job_Category(accessCmd, msSqlCmd);
migratePrintJob(accessCmd, msSqlCmd);
migratePrintJob_Detail1(accessCmd, msSqlCmd);
migratePrintJobLookUp(accessCmd, msSqlCmd);
migratePrintOrder(accessCmd, msSqlCmd);
migrateUserAC(accessCmd, msSqlCmd);
msSqlCmd2.CommandText = "insert into Sequences values('ObjectId'," + ObjectId + ")";
msSqlCmd2.ExecuteNonQuery();
accessCmd.Dispose();
msSqlCmd.Dispose();
accessConn.Close();
msSqlConn.Close();
}
public static void GetAllProtocolIP()
{
string strSql;
try
{
tVehInfo nVehInfo = null;
System.Data.OleDb.OleDbCommand GetProTypeCommand = null;
System.Data.OleDb.OleDbDataReader GetProTypeReader = null;
strSql = "select distinct IpAddress,type,TaxiNo from vehicle";
GetProTypeCommand = myConn.CreateCommand();
GetProTypeCommand.CommandText = strSql;
GetProTypeReader = GetProTypeCommand.ExecuteReader();
CarInfo_Hash = new Hashtable();
while (GetProTypeReader.Read())
{
nVehInfo = new tVehInfo();
nVehInfo.ipaddress = GetProTypeReader["IpAddress"].ToString();
if (GetProTypeReader["type"].ToString().ToUpper() == "TCP")
{
nVehInfo.isendtype = 1;
}
else
{
nVehInfo.isendtype = 0;
}
if (CarInfo_Hash.ContainsKey((string)GetProTypeReader["IpAddress"]))
{
CarInfo_Hash[(string)GetProTypeReader["IpAddress"]] = nVehInfo;
}
else
{
CarInfo_Hash.Add((string)GetProTypeReader["IpAddress"], nVehInfo);
}
}
GetProTypeReader.Close();
GetProTypeReader = null;
GetProTypeCommand.Dispose();
}
catch (Exception ce)
{
GprsServer.WriteErrLog("GetAllProtocolIP", ce.Message.ToString() + ce.StackTrace.ToString());
if (myConn.State.ToString() == "Closed")
{
Conndb();
}
return;
}
}
/// <summary>
/// 读取Excel文件
/// </summary>
/// <param name="filepath">文件路径</param>
/// <returns>DataTable</returns>
public static DataTable ReadExcel(string filename, bool isHdR)
{
string hdr = "N0";
if (isHdR)
{
hdr = "YEX";
}
System.Data.DataSet itemDS = new DataSet();
if (filename.Trim().ToUpper().EndsWith("XLS") || filename.Trim().ToUpper().EndsWith("XLSX"))
{
string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;HdR=" + hdr + ";\"";
System.Data.OleDb.OleDbConnection conn = null;
System.Data.OleDb.OleDbCommand oledbCommd = null;
try
{
conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = connStr;
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//判断连接Excel sheet名
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
string sqlText = "select * from [" + dr["TABLE_NAME"] + "]";
oledbCommd = new System.Data.OleDb.OleDbCommand(sqlText, conn);
oledbCommd.CommandTimeout = 100000;
//执行
System.Data.OleDb.OleDbDataAdapter oledbDA = new System.Data.OleDb.OleDbDataAdapter(oledbCommd);
oledbDA.Fill(itemDS);
}
}
catch
{ }
finally
{
//释放
oledbCommd.Dispose();
conn.Close();
}
//创建连接
}
return(itemDS.Tables[0]);
}
//打开恢复数据库专用的数据连接,用master数据库
public string DBCreate(string str_Sql)
{
string myConnectionStr = "Provider=SQLOLEDB.1;Persist Security Info=False;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID="
+ ConfigurationSettings.AppSettings["WorkstationSet"]
+ ";Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=master"
+ ";Data Source=" + SystemInformation.ComputerName + "\\MSDEDH"
+ ";User ID=" + ConfigurationSettings.AppSettings["DatabaseUser"]
+ ";Password="******"DatabasePassword"];
myConnection = new System.Data.OleDb.OleDbConnection(myConnectionStr);
try
{
myConnection.Open();
}
catch (SqlException e)
{
string errorMessage = e.Message;
return(errorMessage);
}
if (HaveDB() == false)
{
myCommand = new System.Data.OleDb.OleDbCommand("CREATE DATABASE CityEduManage", myConnection);
myCommand.ExecuteNonQuery();
myCommand.Dispose();
}
//断开CityEduManage的一切连接
//myCommand = new SqlCommand(str_Sql,myConnection);
string str_Sql_DisConnect = "declare hcforeach cursor global for select 'kill '+rtrim(spid) from master.dbo.sysprocesses where dbid=db_id('"
+ ConfigurationSettings.AppSettings["Database"]
+ "') exec sp_msforeach_worker '?'";
myCommand = new System.Data.OleDb.OleDbCommand(str_Sql_DisConnect, myConnection);
myCommand.ExecuteNonQuery();
myCommand.Dispose();
myCommand = new System.Data.OleDb.OleDbCommand(str_Sql, myConnection);
myCommand.ExecuteNonQuery();
myCommand.Dispose();
myConnection.Close();
return("OK");
}
public void Sil(String tabloAdi, String SutunAd, int id)
{
try
{
String cmdStr = String.Format("DELETE * FROM {0} WHERE {1}=@idm", tabloAdi, SutunAd);
Kontrol();
cmd = new OleDbCommand(cmdStr, bglnti);
cmd.Parameters.Add("@idm", OleDbType.Integer).Value = id;
int i = cmd.ExecuteNonQuery();
MessageBox.Show(String.Format("{0} kadar satır silindi.", i), "Sonuç");
}
catch (Exception e) { MessageBox.Show(e.Message); }
finally
{
cmd.Parameters.Clear();
cmd.Dispose();
bglnti.Close();
}
}
public static int SaveRecord(string sql)
{
const int rv = 0;
try
{
string connectionString = ConfigurationManager.ConnectionStrings["LA3Access"].ConnectionString;
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
var cmGetID = new OleDbCommand("SELECT @@IDENTITY", conn);
var comm = new OleDbCommand(sql, conn) { CommandType = CommandType.Text };
comm.ExecuteNonQuery();
var ds = new DataSet();
var adapt = new OleDbDataAdapter(cmGetID);
adapt.Fill(ds);
adapt.Dispose();
cmGetID.Dispose();
return int.Parse(ds.Tables[0].Rows[0][0].ToString());
}
}
catch (Exception)
{
}
return rv;
}
