C#开发学习笔记:C#中各种数据库参数化SQL语句

1.DB2数据库

string strConn = "Provider=IBMDADB2;Data Source=数据库;UID=用户名;PWD=密码;";
using (OleDbConnection conn = new OleDbConnection(strConn))
{
     string strSql = @" SELECT COUNT(*) FROM 表名 T WHERE T.字段名=? ";//DB2数据库使用?当做参数,因此参数值添加应严格按照顺序添加
        OleDbCommand cmd = new OleDbCommand(strSql, conn);
        foreach (DataRow dr in (this.gridControl1.DataSource as DataTable).Rows)
        {
            if (dr["DATA_STATUS"].ToString() != "注册成功")
            {
                cmd.Parameters.AddWithValue("字段名", dr["字段名"].ToString());
                try
                {
                        conn.Open();

                        strSql = @" SELECT COUNT(*) FROM 表名 T WHERE T.字段名1=? AND T.字段名2=?";
                        cmd.CommandText = strSql;
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("字段名1", dr["字段名1"].ToString());
                        cmd.Parameters.AddWithValue("字段名2", dr["字段名2"].ToString());
                        if (cmd.ExecuteScalar().ToString() == "0")
                        {
                            string sql_ins = " INSERT INTO 表名 ( " +
                                              "  字段名1  " +
                                              "  ,字段名2 " +
                                              "  ,字段名3 " +
                                              "  ,字段名4 " +
                                              "  ) VALUES ( " +
                                              "   ? " +
                                              "  ,? " +
                                              "   ,? " +
                                              "   ,? " +
                                              "  )";
                            cmd.CommandText = sql_ins;
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddWithValue("字段名1", dr["字段名1"].ToString());
                            cmd.Parameters.AddWithValue("字段名2", dr["字段名2"].ToString());
                            cmd.Parameters.AddWithValue("字段名3", dr["字段名3"].ToString());
                            cmd.Parameters.AddWithValue("字段名4", dr["字段名4"].ToString());

                            int count = cmd.ExecuteNonQuery();
                        }
                            
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    dr["DATA_STATUS"] = ex.Message;
                }
        }
} 

 

2.Oracle数据库

//Oracle数据库使用:进行参数化sql语句的查询
string sql = "SELECT * FROM 表名 T WHERE T.SERVICE_NAME=:SERVICE_NAME";
DBCommand comm = new DBCommand(DBConnection.conn);
comm.CommandText = sql;
comm.Parameter("SERVICE_NAME", ServiceName);