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