292
DataGridView数据更新至数据库-逐条更新
首先判断是否存在指定记录，存在则执行更新语句，不存在则执行插入语句。主要用到三个函数：
public class PubVariant
{
public static string strUpdateSql = "update CorrespondFields set CadField = @CadField,FieldType = @FieldType,CADTYPE = @CADTYPE"
+ " where SdeLayerName = @SdeLayerName and CadLayerName = @CadLayerName and SdeField = @SdeField";
public static string strInsertSql = "insert into CorrespondFields values(@SdeLayerName,@CadLayerName,@SdeField,@CadField,@FieldType,@CADTYPE)";
}
/// <summary>
/// 判断数据库是否有指定键值的记录
/// </summary>
/// <param name="str">键值</param>
/// <returns>是否存在记录的布尔值</returns>
public static bool ExistsRecord(string str)
{
string strSql = "select * from CorrespondFields where SdeField = '" + str + "' and SdeLayerName = '" + PubVariant.sdeLayerName
+ "' and CadLayerName = '" + PubVariant.cadLayerName + "'";
using (SqlConnection connection = new SqlConnection(PubVariant.ConnectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandText = strSql;
cmd.CommandType = CommandType.Text;
SqlDataReader datareader = cmd.ExecuteReader();
return datareader.HasRows;
}
}
/// <summary>
/// 执行带参数的Sql语句
/// </summary>
/// <param name="sqlParas">sql参数数组</param>
/// <param name="strSql">要执行的sql语句</param>
public static void ExecuteSql(SqlParameter[] sqlParas, string strSql)
{
using (SqlConnection connection = new SqlConnection(PubVariant.ConnectionString))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand(strSql, connection))
{
foreach (SqlParameter sp in sqlParas)
{
cmd.Parameters.Add(sp);
}
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 用datagridview的数据更新数据库
/// </summary>
/// <param name="dgv">datagridview</param>
/// <returns>更新是否成功</returns>
public static bool UpdataFromDGVtoDB(DataGridView dgv)
{
try
{
for (int i = 0; i < dgv.Rows.Count - 1; i++)
{
string strCADTYPE;
if (dgv.Rows[i].Cells[1].Value.ToString().StartsWith("["))
{
strCADTYPE = "1";
}
else
{
strCADTYPE = "2";
}
SqlParameter[] sqlParas = new SqlParameter[]
{
new SqlParameter("@SdeLayerName", PubVariant.sdeLayerName),
new SqlParameter("@CadLayerName", PubVariant.cadLayerName),
new SqlParameter("@SdeField", dgv.Rows[i].Cells[0].Value.ToString()),
new SqlParameter("@CadField", dgv.Rows[i].Cells[1].Value.ToString()),
new SqlParameter("@FieldType", dgv.Rows[i].Cells[2].Value.ToString()),
new SqlParameter("@CADTYPE", strCADTYPE)
};
if (ExistsRecord(dgv.Rows[i].Cells[0].Value.ToString()))
{
ExecuteSql(sqlParas, PubVariant.strUpdateSql);
}
else
{
ExecuteSql(sqlParas, PubVariant.strInsertSql);
}
}
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "系统提示");
return false;
}
}
最后更新：2017-04-03 05:38:54