56,677
社区成员
发帖
与我相关
我的任务
分享
private void Running()
{
df = new DataFactry("DataBase.SQL.clsSQL");
df.OnReportEvent += new DataFactry.ReportEventHandler(df_OnReportEvent); //声明事件
DateTime dt = DateTime.Now;
//从文件中读取数据,然后导入数据库
using (StreamReader Reader = new StreamReader(path))
{
string sLine = Reader.ReadLine();
while (sLine != null)
{
if (!sLine.Equals("")) //读取一行数据
{
//setMessage(sLine);
switch (doFlag)
{
case 0: //价格
df.savePrice(sLine, dt);
break;
case 1: //商品
df.saveProduct(sLine);
break;
case 2: //目录
df.saveCataLog(sLine);
break;
}
}
sLine = Reader.ReadLine();
}
Reader.Close();
}
setMessage("数据导入完毕,请查看数据库");
try { thread.Abort(); } //结束线程
catch { }
finally{thread = null;}
}
/// <summary>
/// 保存目录数据
/// </summary>
/// <param name="msg">从文件中读取的目录数据</param>
/// <returns>0:保存成功,其他:失败</returns>
public int saveCataLog(string msg)
{
int rst = 0;
stCatalog c = new stCatalog();
if (updateCatalog(msg, ref c) == 1)
rst = db.setCataLog(c);
OnReportEvent(this, new ReportEventArgss(string.Format("目录[{0}]信息更新{1}.", c.caption, (rst == 0 ? "失败" : "成功"))));
return rst;
}
public int setCataLog(stCatalog catalog)
{
//throw new NotImplementedException();
//构造合适的SQL语句
//执行insert命令(在函数EditData)
pramsCatalog[0].Value = catalog.url;
pramsCatalog[1].Value = catalog.id;
pramsCatalog[2].Value = catalog.cataLogType;
pramsCatalog[3].Value = catalog.parentID;
pramsCatalog[4].Value = catalog.parentType;
pramsCatalog[5].Value = catalog.caption;
if (ExecuteProcedure("SaveCatalog", pramsCatalog)) return 1;
else return 0;
}
/// <summary>
/// 执行命令或存储过程,返回MySqlDataReader对象
/// 注意MySqlDataReader对象使用完后必须Close以释放MySqlConnection资源
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="cmdType">命令类型(存储过程或SQL语句)</param>
/// <param name="cmdText">SQL语句或存储过程名</param>
/// <param name="cmdParms">MySqlCommand参数数组</param>
/// <returns></returns>
public bool ExecuteProcedure (string ProcedureName, MySqlParameter[] cmdParms)
{
//打开数据库链接,如果非关闭状态 ,就不用再打开
try
{
if (mysqlConnection.State == System.Data.ConnectionState.Closed ) mysqlConnection.Open();
}
catch(MySqlException e)
{
mysqlConnection.Close();
return false;
}
//执行存储过程
try
{
MySqlCommand myCommand = new MySqlCommand(ProcedureName,mysqlConnection);
myCommand.CommandType = CommandType.StoredProcedure;
foreach (MySqlParameter p in cmdParms)
{
myCommand.Parameters.Add(p);
}
myCommand.ExecuteNonQuery();
}
catch(MySqlException e)
{
mysqlConnection.Close();
return false;
}
return true;
}
begin
DECLARE iCount int DEFAULT 0 ;
SELECT COUNT(*) FROM catalog WHERE url = in_url into iCount;
IF iCount = 0 THEN
INSERT INTO catalog VALUES(in_url,in_id,in_cataLogType,in_parentID,in_parentType,in_caption);
ELSE
UPDATE catalog SET id=in_id,cataLogType=in_cataLogType,parentID=in_parentID,parentType=in_parentType,caption=in_caption WHERE url=in_url;
END IF;
end
begin
DECLARE iCount int DEFAULT 0 ;
SELECT COUNT(*) FROM catalog WHERE url = url into iCount;
IF iCount = 0 THEN
INSERT INTO catalog VALUES(url,id,cataLogType,parentID,parentType,caption);
ELSE
UPDATE catalog SET id=id,cataLogType=cataLogType,parentID=parentID,parentType=parentType,caption=caption WHERE url=url;
END IF;
end
CREATE DEFINER = CURRENT_USER FUNCTION `NewProc`(`url` varchar,`id` int,`cataLogType` int,`parentID` int,`parentType` int,`caption` varchar)
RETURNS int(32)
BEGIN
DECLARE iCount int DEFAULT 0 ;
iCount = SELECT COUNT(*) FROM catalog WHERE url = url;
IF iCount == 0 THEN
INSERT INTO catalog VALUES(url,id,cataLogType,parentID,parentType,caption);
RETURN 1;
ELSE
UPDATE catalog SET id=id,cataLogType=cataLogType,parentID=parentID,parentType=parentType,caption=caption WHERE url=url;
RETURN 0;
END IF;
END;;
//问题是保存上面的存储过处出错。
提示:1064-You have an error in your SQL syntax;check the manual that corresponds to your MySQL server version for the right syntax to use near 'id' int ......
可能是ID是关键字,不让用,请问是不是这个回事呢?
上面是我的存储过程,catalog是一个表名。有字段url,id,cataLogType,parentID,parentType,caption
我的参数是
MySqlParameter[] pramsCatalog ={new MySqlParameter(@"?url", MySqlDbType.VarChar,256),
new MySqlParameter(@"?id",MySqlDbType.Int32),
new MySqlParameter(@"?cataLogType",MySqlDbType.Int32),
new MySqlParameter(@"?parentID",MySqlDbType.Int32),
new MySqlParameter(@"?parentType", MySqlDbType.Int32),
new MySqlParameter(@"?caption",MySqlDbType.VarChar,256)};