/// <summary>
/// 判断用户登录
/// </summary>
/// <param name="sCode"></param> 编号
/// <param name="sUser"></param> 用户名
/// <param name="sPass"></param> 密码
/// <param name="dba"></param> 数据库操作
/// <returns></returns> 返回查询结果值
public int loginJudge(string sCode, string sUser, string sPass, dbAccess dba)
{
String sLog = "Select count(1) as usercount from users where user_code ='"
+ sCode + "' and user_detail = '" + sUser
+ "' and user_pwd = '" + sPass + "'";
return(Convert.ToInt32(getQueryResultBySQL(sLog)));
}
private void button2_Click(object sender, EventArgs e)
{
try
{
if (txtServer.Text != "" || txtDb.Text != "" || txtDbuser.Text != "" || txtDbPass.Text != "")
{
dba = new dbAccess();
dba.conn.ConnectionString = GetConnectString();
if (dba.OpenConn())
{
//先判断有误表 AeraCodeNation 没有的话先创建
String tableNameStr = "select count(1) from sysobjects where name = 'AeraCodeNation'";
SqlCommand cmd = new SqlCommand(tableNameStr, dba.conn);
int result = Convert.ToInt32(cmd.ExecuteScalar());
if (result == 0)
{
//未创建 开始创建表 AeraCodeNation
if (dba.conn.State == ConnectionState.Open)
{
dba.conn.Close();
}
dba.OpenConn();
string sql222 = "CREATE TABLE AeraCodeNation([Code][varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,[Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,CONSTRAINT[PK_AeraCodeNation] PRIMARY KEY CLUSTERED([Code] ASC)WITH(IGNORE_DUP_KEY = OFF) ON[PRIMARY]) ON[PRIMARY]";
cmd = new SqlCommand(sql222, dba.conn);
cmd.ExecuteNonQuery();
}
}
}
else
{
MessageBox.Show("数据库信息不能为空！");
return;
}
if (txtfilename.Text != "")
{
string[] lines = System.IO.File.ReadAllLines(txtfilename.Text);
if (lines.Length > 0)
{
foreach (var item in lines)
{
string[] tmp = item.Split('#');
string code = tmp[1];
string name = tmp[0];
string InfoValueStr = "insert into AeraCodeNation values('" + code + "','" + name + "')";
dba.UpdateDbBySQL(InfoValueStr);
}
}
}
else
{
MessageBox.Show("文件名不能为空！");
}
MessageBox.Show("数据导入完成");
}
catch (Exception)
{
throw;
}
}
private void button3_Click(object sender, EventArgs e)
{
try
{
if (txtServer.Text != "" || txtDb.Text != "" || txtDbuser.Text != "" || txtDbPass.Text != "")
{
dba = new dbAccess();
dba.conn.ConnectionString = GetConnectString();
if (dba.OpenConn())
{
//判断表中有无数据列GB_CODE
string sql223 = "select* from syscolumns where id = object_id('Organization') and name = 'GB_CODE'";
SqlCommand cmd = new SqlCommand(sql223, dba.conn);
object pp = cmd.ExecuteScalar();
if (pp == null)
{
//没有数据列，往表中Organization插入列 GB_CODE
string SQL11 = "ALTER TABLE Organization ADD GB_CODE [varchar](255) COLLATE Chinese_PRC_CI_AS NULL";
cmd = new SqlCommand(SQL11, dba.conn);
cmd.ExecuteNonQuery();
}
else
{
//已经有数据列
Console.WriteLine("GB_CODE已经存在");
}
}
}
else
{
MessageBox.Show("数据库信息不能为空！");
return;
}
if (txtfilename.Text != "")
{
string[] lines = System.IO.File.ReadAllLines(txtfilename.Text);
string MediaSql = "select ORG_ID,ORG_DETAIL,ORG_CODEA from Organization";
DataTable dtMedia = dba.getQueryInfoBySQL(MediaSql);
if (lines.Length > 0)
{
foreach (var item in lines)
{
string[] tmp = item.Split('#');
string code = tmp[1];
string name = tmp[0].Trim();
string selectString = "";
if (code.Substring(2, 10) == "0000000000" || code.Substring(4, 8) == "00000000")
{
//省或者市
selectString = string.Format("ORG_DETAIL='{0}'", name);
}
else
{
string tunerscode = "P" + code.Substring(0, 2) + "Q" + code.Substring(2, 2) + "C" + code.Substring(4, 2);
selectString = string.Format("ORG_DETAIL='{0}' and ORG_CODEA like '{1}%'", name, tunerscode);
}
DataRow[] dr = dtMedia.Select(selectString);
if (dr != null && dr.Length > 0)
{
if (dr.Length == 1)
{
string InfoValueStr = "UPDATE Organization set GB_CODE ='" + code + "' where ORG_ID='" + dr[0][0].ToString() + "'";
dba.UpdateDbBySQL(InfoValueStr);
}
}
}
}
}
else
{
MessageBox.Show("文件名不能为空！");
}
MessageBox.Show("数据导入完成");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// 更新或新增记录 特殊用法
/// </summary>
/// <param name="sTable"></param> 表名
/// <param name="sColValTye"></param> 字符串数组，组成方式为 “字段名，值,字段类型”
/// <param name="sWhere"></param> 更新条件
/// <param name="dba"></param> 数据库处理对象
/// <returns></returns> 返回值，成功1、失败-1
public static int UpdateOrInsert(string sTable, int iMode, List <string> sColValTye, string sWhere, dbAccess dba)
{
try
{
string sSql = "", sSql2 = "", sTmp = "";
string[] strTmp;
if (iMode == 1) //新增
{
for (int i = 0; i < sColValTye.Count; i++)
{
if (sColValTye[i] == null || sColValTye[i].Length == 0)
{
continue;
}
strTmp = sColValTye[i].Split(',');
if (strTmp.Length == 3)
{
if (strTmp[2].ToLower() == "string")
{
sSql += strTmp[0] + ",";
sSql2 += "'" + strTmp[1] + "',";
}
else if (strTmp[2].ToLower() == "datetime")
{
sSql += strTmp[0] + ",";
sSql2 += "cast('" + strTmp[1] + "' as datetime),";
}
else if (strTmp[2].ToLower() == "int")
{
sSql += strTmp[0] + ",";
sSql2 += strTmp[1] + ",";
}
}
else if (strTmp.Length > 3)
{
sTmp = "";
for (int j = 1; j < strTmp.Length - 1; j++)
{
sTmp = sTmp + strTmp[j] + ",";
}
sTmp = sTmp.TrimEnd(',');
if (strTmp[strTmp.Length - 1].ToLower() == "string")
{
sSql += strTmp[0] + ",";
sSql2 += "'" + sTmp + "',";
}
else if (strTmp[strTmp.Length - 1].ToLower() == "datetime")
{
sSql += strTmp[0] + ",";
sSql2 += "cast('" + strTmp[1] + "' as datetime),";
}
else if (strTmp[strTmp.Length - 1].ToLower() == "int")
{
sSql += strTmp[0] + ",";
sSql2 += sTmp + ",";
}
}
}
if (sSql.Length > 0)
{
sSql = sSql.TrimEnd(',');
sSql2 = sSql2.TrimEnd(',');
sSql = "Insert into " + sTable + "(" + sSql + ") values (" + sSql2 + ")";
}
}
else if (iMode == 2) //修改
{
for (int i = 0; i < sColValTye.Count; i++)
{
if (sColValTye[i] == null || sColValTye[i].Length == 0)
{
continue;
}
strTmp = sColValTye[i].Split(',');
if (strTmp.Length == 3)
{
if (strTmp[2].ToLower() == "string")
{
sSql += strTmp[0] + " = '" + strTmp[1] + "',";
}
else if (strTmp[2].ToLower() == "datetime")
{
sSql += strTmp[0] + " = cast('" + strTmp[1] + "' as datetime),";
}
else if (strTmp[2].ToLower() == "int")
{
sSql += strTmp[0] + " = " + strTmp[1] + ",";
}
}
else if (strTmp.Length > 3)
{
sTmp = "";
for (int j = 1; j < strTmp.Length - 1; j++)
{
sTmp = sTmp + strTmp[j] + ",";
}
sTmp = sTmp.TrimEnd(',');
if (strTmp[strTmp.Length - 1].ToLower() == "string")
{
sSql += strTmp[0] + " = '" + sTmp + "',";
}
else if (strTmp[strTmp.Length - 1].ToLower() == "datetime")
{
sSql += strTmp[0] + " = cast('" + strTmp[1] + "' as datetime),";
}
else if (strTmp[strTmp.Length - 1].ToLower() == "int")
{
sSql += strTmp[0] + " = " + sTmp + ",";
}
}
}
if (sSql.Length > 0)
{
sSql = sSql.TrimEnd(',');
sSql = "Update " + sTable + " Set " + sSql + (sWhere.Length > 0 ? sWhere : "");
}
}
if (sSql.Length > 0 && dba.UpdateDbBySQL(sSql) != 1)
{
return(-1);
}
}
catch
{
return(-1);
}
return(1);
}