2008年12月25日工作日记

1．不存在则插入

错误的写法

INSERT INTO AppTitle(Id,Mac,CompanyId,AppTitle,GatherTime) Values('23482109-80d4-4830-8143-9e9e08acbbbc','00:16:D3:0B:61:8C','0001001','博宇计算机监控程序

鱼鱼桌面2.2.1

','2008-12-18 14:52:36')

WHERE NOT EXISTS(SELECT * FROM AppTitle WHERE Id='23482109-80d4-4830-8143-9e9e08acbbbc')

正确的写法

IF NOT EXISTS(SELECT * FROM AppTitle WHERE Id='23482109-80d4-4830-8143-9e9e08acbbbc')

INSERT INTO AppTitle(Id,Mac,CompanyId,AppTitle,GatherTime) Values('23482109-80d4-4830-8143-9e9e08acbbbc','00:16:D3:0B:61:8C','0001001','博宇计算机监控程序

鱼鱼桌面2.2.1

','2008-12-18 14:52:36')

 

2.向数据库插入图片

cmdstring = string.Format(@"IF NOT EXISTS(SELECT * FROM Image WHERE Id='{0}')

                                                            INSERT INTO Image(Id,Mac,CompanyId,AppId,ImageType,Image,GatherTime) Values(@Id,@Mac,@CompanyId,@AppId,@ImageType,@Image,@GatherTime)", reader["Id"].ToString());

                SqlParameter[] paramters = new SqlParameter[7];

                paramters[0] = new SqlParameter();

                paramters[0].ParameterName = "@Id";

                paramters[0].SqlDbType = SqlDbType.VarChar;

                paramters[0].Value=reader["Id"].ToString();

                paramters[1] = new SqlParameter();

                paramters[1].ParameterName="@Mac";

                paramters[1].SqlDbType = SqlDbType.VarChar;

                paramters[1].Value=reader["CompanyId"].ToString();

                paramters[2] = new SqlParameter();

                paramters[2].ParameterName="@CompanyId";

                paramters[2].SqlDbType = SqlDbType.VarChar;

                paramters[2].Value=reader["CompanyId"].ToString();

                paramters[3] = new SqlParameter();

                paramters[3].ParameterName="@AppId";

                paramters[3].SqlDbType = SqlDbType.VarChar;

                paramters[3].Value=reader["AppId"].ToString();

                paramters[4] = new SqlParameter();

                paramters[4].ParameterName="@ImageType";

                paramters[4].SqlDbType = SqlDbType.SmallInt;

                paramters[4].Value=Convert.ToInt32( reader["ImageType"]);

                paramters[5] = new SqlParameter();

                paramters[5].ParameterName="@Image";

                paramters[5].SqlDbType = SqlDbType.Image;

                paramters[5].Value= reader["Image"];

                paramters[6] = new SqlParameter();

                paramters[6].ParameterName="@GatherTime";

                paramters[6].SqlDbType = SqlDbType.DateTime;

                paramters[6].Value=Convert.ToDateTime( reader["GatherTime"]);

                SqlHelper.ExecuteNonQuery(constring, CommandType.Text, cmdstring, paramters);