dzpub.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace dztest.pub
{
class dzpub
{
private static string m_userid;
private static string m_username;
private static string m_userpwd;
private static string m_usersts;
/// <summary>
/// 操作员代码
/// </summary>
public static string p_userid
{
get { return m_userid; }
set { m_userid = value; }
}
/// <summary>
/// 操作员名称
/// </summary>
public static string p_username
{
get { return m_username; }
set { m_username = value; }
}
/// <summary>
/// 操作员密码
/// </summary>
public static string p_userpwd
{
get { return m_userpwd; }
set { m_userpwd = value; }
}
/// <summary>
/// 操作员
/// </summary>
public static string p_usersts
{
get { return m_usersts; }
set { m_usersts = value; }
}
}
}
dzdb.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using dztest.pub;
namespace dztest.pub
{
class DataBase
{
private readonly SqlCommand m_Cmd;
private readonly SqlConnection m_Conn;
/// <summary>
/// 创建数据库连接和SqlCommand实例
/// </summary>
public DataBase()
{
string strServer="127.0.0.1";
string strUserID = "sa";
string strPwd = "sasasa";
string strBD = "tempdb";
//数据库连接字符串
string strConn = "Server = " + strServer + ";Database=" + strBD + ";User id=" + strUserID + ";PWD=" + strPwd;
try
{
m_Conn = new SqlConnection(strConn);
m_Cmd = new SqlCommand();
m_Cmd.Connection = m_Conn;
}
catch (Exception e)
{
throw e;
}
}
public SqlConnection Conn
{
get { return m_Conn; }
}
public SqlCommand Cmd
{
get { return m_Cmd; }
}
/// <summary>
/// 通过Transact-SQL语句提交数据
/// </summary>
/// <param name="strSql"> Transact-SQL语句 </param>
/// <returns> 受影响的行数 </returns>
public int ExecDataBySql(string strSql)
{
int intReturnValue;
m_Cmd.CommandType = CommandType.Text;
m_Cmd.CommandText = strSql;
try
{
if (m_Conn.State == ConnectionState.Closed)
{
m_Conn.Open();
}
intReturnValue = m_Cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
m_Conn.Close(); //连接关闭,但不释放掉该对象所占的内存单元
}
return intReturnValue;
}
/// <summary>
/// 多条Transact-SQL语句提交数据
/// </summary>
/// <param name="strSqls"> 使用List泛型封装多条SQL语句 </param>
/// <returns> bool值(提交是否成功) </returns>
public bool ExecDataBySqls(List<string> strSqls)
{
bool booIsSucceed;
if (m_Conn.State == ConnectionState.Closed)
{
m_Conn.Open();
}
SqlTransaction sqlTran = m_Conn.BeginTransaction();
try
{
m_Cmd.Transaction = sqlTran;
foreach (string item in strSqls)
{
m_Cmd.CommandType = CommandType.Text;
m_Cmd.CommandText = item;
m_Cmd.ExecuteNonQuery();
}
sqlTran.Commit();
booIsSucceed = true; //表示提交数据库成功
}
catch
{
sqlTran.Rollback();
booIsSucceed = false; //表示提交数据库失败!
}
finally
{
m_Conn.Close();
strSqls.Clear();
}
return booIsSucceed;
}
/// <summary>
/// 通过Transact-SQL语句得到DataSet实例
/// </summary>
/// <param name="strSql"> Transact-SQL语句 </param>
/// <param name="strTable"> 相关的数据表 </param>
/// <returns> DataSet实例的引用 </returns>
public DataSet GetDataSet(string strSql, string strTable)
{
DataSet ds = null;
try
{
SqlDataAdapter sda = new SqlDataAdapter(strSql, m_Conn);
ds = new DataSet();
sda.Fill(ds, strTable);
}
catch (Exception e)
{
throw e;
}
return ds;
}
/// <summary>
/// 通过Transact-SQL语句得到SqlDataReader实例
/// </summary>
/// <param name="strSql"> Transact-SQL语句 </param>
/// <returns> SqlDataReader实例的引用 </returns>
public SqlDataReader GetDataReader(string strSql)
{
SqlDataReader sdr;
m_Cmd.CommandType = CommandType.Text;
m_Cmd.CommandText = strSql;
try
{
if (m_Conn.State == ConnectionState.Closed)
{
m_Conn.Open();
}
sdr = m_Cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception e)
{
throw e;
}
//sdr对象和m_Conn对象暂时不能关闭和释放掉,否则在调用时无法使用
//待使用完毕sdr,再关闭sdr对象(同时会自动关闭关联的m_Conn对象)
//m_Conn的关闭是指关闭连接通道,但连接对象依然存在
//m_Conn的释放掉是指销毁连接对象
return sdr;
}
/// <summary>
/// 重新封装ExecuteScalar方法,得到结果集中的第一行的第一列
/// </summary>
/// <param name="strSql"> Transact-SQL语句 </param>
/// <returns> 结果集中的第一行的第一列 </returns>
public object GetSingleObject(string strSql)
{
object obj = null;
m_Cmd.CommandType = CommandType.Text;
m_Cmd.CommandText = strSql;
try
{
if (m_Conn.State == ConnectionState.Closed)
{
m_Conn.Open();
}
obj = m_Cmd.ExecuteScalar();
}
catch (Exception e)
{
throw e; //向上一层抛出异常(上一层使用try{}catch{})或立刻中断(上一层未使用try{}catch{})
}
finally
{
m_Conn.Close();
}
return obj;
}
/// <summary>
/// 通过Transact-SQL语句,得到DataTable实例
/// </summary>
/// <param name="strSqlCode"> Transact-SQL语句 </param>
/// <param name="strTableName"> 数据表的名称 </param>
/// <returns> DataTable实例的引用 </returns>
public DataTable GetDataTable(string strSqlCode, string strTableName)
{
DataTable dt = null;
SqlDataAdapter sda = null;
try
{
sda = new SqlDataAdapter(strSqlCode, m_Conn);
dt = new DataTable(strTableName);
sda.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
return dt; //dt.Rows.Count可能等于零
}
/// <summary>
/// 通过存储过程,得到DataTable实例
/// </summary>
/// <param name="strProcedureName"> 存储过程名称 </param>
/// <param name="inputParameters"> 存储过程的参数数组 </param>
/// <returns> DataTable实例的引用 </returns>
public DataTable GetDataTable(string strProcedureName, SqlParameter[] inputParameters)
{
DataTable dt = new DataTable();
SqlDataAdapter sda = null;
try
{
m_Cmd.CommandType = CommandType.StoredProcedure;
m_Cmd.CommandText = strProcedureName;
sda = new SqlDataAdapter(m_Cmd);
m_Cmd.Parameters.Clear();
foreach (SqlParameter param in inputParameters)
{
param.Direction = ParameterDirection.Input;
m_Cmd.Parameters.Add(param);
}
sda.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
return dt; //dt.Rows.Count可能等于零
}
}
}
form1.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using dztest.pub;
namespace dztest
{
public partial class Form1 : Form
{
private readonly DataBase db = new DataBase();
private SqlDataReader sdr;
public Form1()
{
InitializeComponent();
}
private void bt_ok_Click(object sender, EventArgs e)
{
errorProvider1.Clear();
if (String.IsNullOrEmpty(tb_userid.Text.Trim()))
{
errorProvider1.SetError(tb_userid,"用戶名不能為空");
return;
}
if (String.IsNullOrEmpty(tb_pwd.Text.Trim()))
{
errorProvider1.SetError(tb_pwd, "密碼不能為空");
return;
}
string strSql = "select * from t01users where t01_userid = '" + tb_userid.Text.Trim() + "'";
try
{
sdr = db.GetDataReader(strSql);
sdr.Read();
if (sdr.HasRows)
{
//AppMain AppForm = new AppMain();
//Hide();
dzpub.p_userid = sdr["t01_userid"].ToString();
dzpub.p_username = sdr["t01_name"].ToString();
dzpub.p_userpwd = sdr["t01_pwd"].ToString();
dzpub.p_usersts = sdr["t01_sts"].ToString();
if (dzpub.p_userpwd.Trim() != tb_pwd.Text.Trim())
{
MessageBox.Show("密碼不正确!", "软件提示");
tb_pwd.Clear();
return;
}
if (dzpub.p_usersts == "D")
{
MessageBox.Show("用户被禁止登入!", "软件提示");
return;
}
//AppForm.Show();
MessageBox.Show(dzpub.p_username + "成功登入系統!", "软件提示");
}
else
{
MessageBox.Show("用户名不正确!", "软件提示");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "软件提示");
throw ex;
}
finally
{
sdr.Close();
}
}
private void bt_close_Click(object sender, EventArgs e)
{
Close();
}
private void tb_userid_KeyDown(object sender, KeyEventArgs e)
{
//登录用户文本框敲回车键
if (e.KeyCode == Keys.Enter)
{
tb_pwd.Focus();
}
}
private void tb_pwd_KeyDown(object sender, KeyEventArgs e)
{
//登录密码文本框敲回车键
if (e.KeyCode == Keys.Enter)
{
bt_ok_Click(sender, e);
}
}
}
}