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;
}
}
/// <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);
}