/// <summary>
/// 执行非查询SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>受影响的记录数</returns>
public override int ExecuteNoQuery(string sql, KdtParameterCollection parameters)
{
try
{
int effected = 0;
// 执行SQL命令
using (OdbcCommand cmd = new OdbcCommand(ReplaceSqlText(sql, parameters), _odbcCn))
{
InitCommand(cmd); // 初始化
// 赋值参数
var hasConvertParams = ConvertToOdbcParameter(parameters);
foreach (var item in hasConvertParams)
{
cmd.Parameters.Add(item.Value);
}
effected = cmd.ExecuteNonQuery();
cmd.Cancel();
cmd.Dispose();
}
return(effected);
}
catch (Exception ex)
{
KdtLoger.Instance.Error(ex);
throw new DataException(string.Format("执行非查询SQL语句错误,原因为:{0}", ex.Message));
}
}
/// <summary>
/// 执行查询,返回查询结果的第一行第一列
/// </summary>
/// <typeparam name="T">返回值类型</typeparam>
/// <param name="sql">SQL查询语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>查询结果的第一行第一列</returns>
public override T ExecuteScalar <T>(string sql, KdtParameterCollection parameters)
{
try
{
T value = default(T);
// 执行SQL命令
using (OdbcCommand cmd = new OdbcCommand(ReplaceSqlText(ReplaceSqlText(sql, parameters), parameters), _odbcCn))
{
InitCommand(cmd); // 初始化
// 赋值参数
var hasConvertParams = ConvertToOdbcParameter(parameters);
foreach (var item in hasConvertParams)
{
cmd.Parameters.Add(item.Value);
}
value = cmd.ExecuteScalar().Convert <T>();
cmd.Cancel();
cmd.Dispose();
}
return(value);
}
catch (Exception ex)
{
KdtLoger.Instance.Error(ex);
throw new DataException(string.Format("执行查询,返回查询结果的第一行第一列错误,原因为:{0}", ex.Message));
}
}
public DataSet SelectSet(string Sql)
{
OdbcConnection Conn = getConnection();
OdbcCommand cmd;
OdbcDataAdapter Dap;
try
{
cmd = new OdbcCommand(Sql, Conn);
cmd.CommandTimeout = 6000;
Dap = new OdbcDataAdapter();
Ds = new DataSet();
Dap.SelectCommand = cmd;
Dap.Fill(Ds, FstCnt, FstCnt + SelCnt, "Table0");
cmd.Cancel();
}
catch (Exception)
{
Ds = new DataSet();
}
Conn.Close();
Dap = null;
cmd = null;
Conn = null;
return(Ds);
}
protected Boolean check_itemExist()
{
Boolean bl_rtnflag = false;
using (ODBCconn = new MyODBCConnection())
{
OdbcCommand cmd = new OdbcCommand(strodbccmd);
cmd.Parameters.Add("item_code", OdbcType.VarChar).Value = txtItemCode.Text;
DataTable dt = ODBCconn.GetData(cmd);
if (dt.Rows.Count > 0) //--資料庫裡面找不到資料
{
txtItemName.Text = dt.Rows[0].Field <string>("f_desc");
txtItemPrice.Text = dt.Rows[0].Field <decimal>("amt_ex").ToString();
bl_rtnflag = true;
}
else
{
PublicLib.showAlert("資料庫無此細項代碼喔");
txtItemCode.Text = "";
txtItemName.Text = "";
txtItemPrice.Text = "";
bl_rtnflag = false;
}
cmd.Cancel();
ODBCconn.Dispose();
}
return(bl_rtnflag);
}
// - /14>
//<Snippet16>
public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
{
OdbcConnection conn = new OdbcConnection(ConnectionString);
OdbcCommand cmd = new OdbcCommand("SELECT Count(*) FROM Users " +
"WHERE Username LIKE ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@UsernameSearch", OdbcType.VarChar, 255).Value = usernameToMatch;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
MembershipUserCollection users = new MembershipUserCollection();
OdbcDataReader reader = null;
totalRecords = 0;
try
{
conn.Open();
totalRecords = (int)cmd.ExecuteScalar();
if (totalRecords <= 0) { return users; }
cmd.CommandText = "SELECT Username, Email, PasswordQuestion," +
" Comment, IsApproved, CreationDate, LastLoginDate," +
" LastActivityDate, LastPasswordChangedDate " +
" FROM Users " +
" WHERE Username LIKE ? AND ApplicationName = ? " +
" ORDER BY Username Asc";
reader = cmd.ExecuteReader();
int counter = 0;
int startIndex = pageSize * pageIndex;
int endIndex = startIndex + pageSize - 1;
while (reader.Read())
{
if (counter >= startIndex)
{
MembershipUser u = GetUserFromReader(reader);
users.Add(u);
}
if (counter >= endIndex) { cmd.Cancel(); }
counter++;
}
}
catch (OdbcException)
{
// Handle exception.
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
return users;
}
void MnuExecuteClick(object sender, EventArgs e)
{
if (mnuExecute.Text == "Execute")
{
if (currentDSN == null)
{
MessageBox.Show("Select a data source", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
lblCurrentStatus.Text = "Running query...";
this.Cursor = Cursors.WaitCursor;
textEditorControl1.Cursor = Cursors.WaitCursor;
start = DateTime.Now;
mnuExecute.Text = "CANCEL";
mnuExecute.ForeColor = Color.Red;
mnuRun.Enabled = false;
mnuSchema.Enabled = false;
Application.DoEvents();
ExecuteArgs args = new ExecuteArgs();
args.ConString = string.Format("DSN={0};Uid={1};Pwd={2};", currentDSN.GetDSNName(), txtUserName.Text, txtPassword.Text);
// If there is selected text use that as the sql source or use all of the text
if (textEditorControl1.ActiveTextAreaControl.SelectionManager.HasSomethingSelected)
{
args.SQL = textEditorControl1.ActiveTextAreaControl.SelectionManager.SelectedText;
}
else
{
args.SQL = textEditorControl1.Text;
}
timer = new Timer();
timer.Interval = 1000;
timer.Tick += delegate(object sender1, EventArgs e1)
{
lblCurrentStatus.Text = "Running query - Elapsed time " + DateTime.Now.Subtract(start).TotalSeconds.ToString("N0") + "s";
};
workerExecute = new BackgroundWorker();
workerExecute.WorkerReportsProgress = true;
workerExecute.WorkerSupportsCancellation = true;
workerExecute.DoWork += new DoWorkEventHandler(ExecuteWorker_DoWork);
workerExecute.RunWorkerCompleted += new RunWorkerCompletedEventHandler(ExecuteWorker_RunWorkerCompleted);
workerExecute.RunWorkerAsync(args);
timer.Start();
}
else
{
com.Cancel();
workerExecute.CancelAsync();
}
}
private void BindGrid() //新增/編輯 套餐
{
DataTable dt = new DataTable();
string strcmd = "SELECT ItemCode FROM HMC_PkgD WHERE 1=0";
if (strPkgCode != null && strPkgCode != "")
{
strcmd = "SELECT ItemCode FROM HMC_PkgD WHERE PkgCode = @Param";
}
SqlCommand cmd = new SqlCommand(strcmd);
if (strPkgCode != null && strPkgCode != "")
{
cmd.Parameters.Add("@Param", SqlDbType.NVarChar).Value = strPkgCode;
}
conn = new MyConnection();
dt = conn.GetData(cmd);
dt.Columns.Add("ItemName", typeof(string));
dt.Columns.Add("ItemPrice", typeof(string));
if (dt.Rows.Count == 0)
{
dt.Rows.Add();
}
else
{
foreach (DataRow row in dt.Rows) //細項名稱
{
string stritemCode = row["ItemCode"].ToString();
using (ODBCconn = new MyODBCConnection())
{
OdbcCommand odbccmd = new OdbcCommand(strodbccmd);
odbccmd.Parameters.Add("item_code", OdbcType.VarChar).Value = stritemCode;
DataTable odbcdt = ODBCconn.GetData(odbccmd);
if (odbcdt.Rows.Count > 0)
{
row["ItemName"] = odbcdt.Rows[0].Field <string>("f_desc");;
row["ItemPrice"] = odbcdt.Rows[0].Field <decimal>("amt_ex");
}
else
{
string strmsg = string.Format("代碼{0},資料庫中無此代碼", stritemCode);
PublicLib.showAlert(strmsg);
}
odbccmd.Cancel();
ODBCconn.Dispose();
}
}
}
ViewState["Pkg_Detail"] = dt;
GridView1.DataSource = dt;
GridView1.DataBind();
}
/// <summary>
/// 读取KEY VALUE值
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public override KeyValueCollection ExecuteKVCollection(string sql, KdtParameterCollection parameters)
{
try
{
KeyValueCollection entity = new KeyValueCollection();
// 执行SQL命令
using (OdbcCommand cmd = new OdbcCommand(ReplaceSqlText(sql, parameters), _odbcCn))
{
InitCommand(cmd); // 初始化
// 赋值参数
var hasConvertParams = ConvertToOdbcParameter(parameters);
foreach (var item in hasConvertParams)
{
cmd.Parameters.Add(item.Value);
}
// 执行填充数据
using (OdbcDataReader reader = cmd.ExecuteReader())
{
entity = GetEntity(reader);
// 反射参数值
ReflectParamValue(parameters, hasConvertParams);
reader.Close();
reader.Dispose();
}
cmd.Cancel();
cmd.Dispose();
}
return(entity);
}
catch (OdbcException me)
{
KdtLoger.Instance.Error(me);
throw new DataException(me.Message);
}
catch (Exception ex)
{
KdtLoger.Instance.Error(ex);
throw new DataException(string.Format("执行SQL查询,返回数据集合错误,原因为:{0}", ex.Message));
}
}
public SqlResault Query(string qr)
{
if (!OnlineMode)
{
string[] qrcommands = qr.Split(';');
SqlResault res = new SqlResault();
foreach (string command in qrcommands)
{
if (command.Trim() != "")
{
using (OdbcCommand DbCommand = Con.CreateCommand())
{
DbCommand.CommandText = command + ";";
using (OdbcDataReader DbReader = DbCommand.ExecuteReader())
{
int cn = 0;
while (DbReader.Read())
{
int fCount = DbReader.FieldCount;
Dictionary <string, string> dict = new Dictionary <string, string>();
for (int i = 0; i < fCount; i++)
{
dict[DbReader.GetName(i)] = DbReader.GetString(i);
}
res[cn] = dict;
cn += 1;
}
DbReader.Close();
DbCommand.Cancel();
DbCommand.Dispose();
}
}
}
}
return(res);
}
else
{
return(Utility.Connection.Send <SqlResault>("query", qr));
}
}
public int ExecuteSql(string Sql)
{
OdbcConnection Conn = getConnection();
OdbcCommand cmd = null;
try
{
cmd = new OdbcCommand(Sql, Conn);
cmd.CommandTimeout = 6000;
ActCnt = cmd.ExecuteNonQuery();
cmd.Cancel();
}
catch (Exception)
{
ActCnt = -9;
}
Conn.Close();
cmd = null;
Conn = null;
return(ActCnt);
}
//
// GetProfileInfo
// Retrieves a count of profiles and creates a
// ProfileInfoCollection from the profile data in the
// database. Called by GetAllProfiles, GetAllInactiveProfiles,
// FindProfilesByUserName, FindInactiveProfilesByUserName,
// and GetNumberOfInactiveProfiles.
// Specifying a pageIndex of 0 retrieves a count of the results only.
//
private ProfileInfoCollection GetProfileInfo(
ProfileAuthenticationOption authenticationOption,
string usernameToMatch,
object userInactiveSinceDate,
int pageIndex,
int pageSize,
out int totalRecords)
{
OdbcConnection conn = new OdbcConnection(connectionString);
// Command to retrieve the total count.
OdbcCommand cmd = new OdbcCommand("SELECT COUNT(*) FROM Profiles WHERE ApplicationName = ? ", conn);
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
// Command to retrieve the profile data.
OdbcCommand cmd2 = new OdbcCommand("SELECT Username, LastActivityDate, LastUpdatedDate, " +
"IsAnonymous FROM Profiles WHERE ApplicationName = ? ", conn);
cmd2.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
// If searching for a user name to match, add the command text and parameters.
if (usernameToMatch != null)
{
cmd.CommandText += " AND Username LIKE ? ";
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = usernameToMatch;
cmd2.CommandText += " AND Username LIKE ? ";
cmd2.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = usernameToMatch;
}
// If searching for inactive profiles,
// add the command text and parameters.
if (userInactiveSinceDate != null)
{
cmd.CommandText += " AND LastActivityDate <= ? ";
cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = (DateTime)userInactiveSinceDate;
cmd2.CommandText += " AND LastActivityDate <= ? ";
cmd2.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = (DateTime)userInactiveSinceDate;
}
// If searching for a anonymous or authenticated profiles,
// add the command text and parameters.
switch (authenticationOption)
{
case ProfileAuthenticationOption.Anonymous:
cmd.CommandText += " AND IsAnonymous = ?";
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = true;
cmd2.CommandText += " AND IsAnonymous = ?";
cmd2.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = true;
break;
case ProfileAuthenticationOption.Authenticated:
cmd.CommandText += " AND IsAnonymous = ?";
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = false;
cmd2.CommandText += " AND IsAnonymous = ?";
cmd2.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = false;
break;
default:
break;
}
// Get the data.
OdbcDataReader reader = null;
ProfileInfoCollection profiles = new ProfileInfoCollection();
try
{
conn.Open();
// Get the profile count.
totalRecords = (int)cmd.ExecuteScalar();
// No profiles found.
if (totalRecords <= 0)
{
return(profiles);
}
// Count profiles only.
if (pageSize == 0)
{
return(profiles);
}
reader = cmd2.ExecuteReader();
int counter = 0;
int startIndex = pageSize * (pageIndex - 1);
int endIndex = startIndex + pageSize - 1;
while (reader.Read())
{
if (counter >= startIndex)
{
ProfileInfo p = GetProfileInfoFromReader(reader);
profiles.Add(p);
}
if (counter >= endIndex)
{
cmd.Cancel();
break;
}
counter++;
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetProfileInfo");
throw new ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
conn.Close();
}
return(profiles);
}
public ArrayList SelectAry(string Sql)
{
OdbcConnection Conn = getConnection();
OdbcCommand cmd;
OdbcDataReader DaRd;
int i = 0;
int col = 0;
string str;
ArrStr = new ArrayList();
TotCnt = 0;
ActCnt = 0;
RtnCnt = 0;
try
{
cmd = new OdbcCommand(Sql, Conn);
cmd.CommandTimeout = 6000;
DaRd = cmd.ExecuteReader();
col = DaRd.FieldCount;
string cnm = "";
for (i = 0; i < col; i++)
{
cnm += DaRd.GetName(i) + ",";
}
while (DaRd.Read())
{
TotCnt++;
if (TotCnt >= FstCnt)
{
ActCnt++;
if ((SelCnt != 0) && (ActCnt > SelCnt))
{
break;
}
str = "";
try
{
for (i = 0; i < col; i++)
{
if (DaRd.GetValue(i) == null)
{
str += "^";
}
else
{
str += DaRd.GetValue(i).ToString().Replace("^", "") + "^";
}
}
ArrStr.Add(str);
}
catch (Exception) { }
}
}
DaRd.Close();
cmd.Cancel();
}
catch (Exception)
{
ArrStr = new ArrayList();
}
Conn.Close();
DaRd = null;
cmd = null;
Conn = null;
return(ArrStr);
}
//
// MembershipProvider.FindUsersByEmail
//
public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
{
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT Count(*) FROM Users " +
"WHERE Email LIKE ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@EmailSearch", OdbcType.VarChar, 255).Value = emailToMatch;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
MembershipUserCollection users = new MembershipUserCollection();
OdbcDataReader reader = null;
totalRecords = 0;
try {
conn.Open();
totalRecords = (int)cmd.ExecuteScalar();
if (totalRecords <= 0)
{
return(users);
}
cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," +
" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " +
" FROM Users " +
" WHERE Email LIKE ? AND ApplicationName = ? " +
" ORDER BY Username Asc";
reader = cmd.ExecuteReader();
int counter = 0;
int startIndex = pageSize * pageIndex;
int endIndex = startIndex + pageSize - 1;
while (reader.Read())
{
if (counter >= startIndex)
{
MembershipUser u = GetUserFromReader(reader);
users.Add(u);
}
if (counter >= endIndex)
{
cmd.Cancel();
}
counter++;
}
}
catch (OdbcException e) {
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "FindUsersByEmail");
throw new ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally {
if (reader != null)
{
reader.Close();
}
conn.Close();
}
return(users);
}
