public static object ExecuteScalarWithSqlParams(IDbConnection conn, IDbTransaction transaction, string sQuery, List <IDbDataParameter> lParams) { try { EnsureConnection(conn); sQuery = CheckQueryByDialect(sQuery); using (IDbCommand cmd = SqlObjectFactory.CreateDbCommand(conn, transaction, sQuery)) { cmd.CommandTimeout = 300; foreach (IDbDataParameter prm in lParams) { cmd.Parameters.Add(prm); } return(cmd.ExecuteScalar()); } } catch (Exception excp) { m_logger.ErrorFormat("ExecuteScalarWithSqlParams() ERROR:{0}\r\n{1}", excp, excp.Message, excp.StackTrace); throw; } }
public static object ExecuteScalar(IDbConnection conn, IDbTransaction transaction, string sFormatQuery, params object[] args) { string sQuery = sFormatQuery; try { EnsureConnection(conn); sQuery = string.Format(sFormatQuery, args); sQuery = CheckQueryByDialect(sQuery); using (IDbCommand cmd = SqlObjectFactory.CreateDbCommand(conn, transaction, sQuery)) { cmd.CommandTimeout = 300; return(cmd.ExecuteScalar()); } } catch (Exception excp) { m_logger.Excp(excp, "ExecuteScalar() ERROR:\r\nQuery:\r\n{0}\r\n", sQuery); return(null); } }
public static IDbCommand GenerateInsertCommand(IDbConnection connection, IDbTransaction transaction, DataTable dt, TableSpecification ts) { string sTemplate = @" INSERT INTO [{0}] ({1}) VALUES ({2}) "; IDbCommand dbc = SqlObjectFactory.CreateDbCommand(connection, transaction, string.Empty); dbc.CommandTimeout = 1000; List <string> lColStrings = new List <string>(); List <string> lSetStrings = new List <string>(); foreach (DataColumn dc in dt.Columns) { if (ts.IsAutoGeneratedIdentity && ts.IdentityNames.Contains(dc.ColumnName.ToLowerInvariant())) { continue; } switch (ConnectionManager.Dialect) { case DatabaseDialect.MsSql: case DatabaseDialect.PgSql: lColStrings.Add(string.Format("[{0}]", dc.ColumnName)); lSetStrings.Add(string.Format("@{0}", dc.ColumnName)); dbc.Parameters.Add(SqlObjectFactory.CreateParameter(dc.ColumnName, null, dc.ColumnName)); break; case DatabaseDialect.MySql: case DatabaseDialect.LtSql: lColStrings.Add(dc.ColumnName); lSetStrings.Add(string.Format("@{0}", dc.ColumnName)); dbc.Parameters.Add(SqlObjectFactory.CreateParameter(dc.ColumnName, null, dc.ColumnName)); break; default: Debug.Assert(false); break; } } string sCmdQuery = string.Format(sTemplate, ts.TableName, string.Join(",", lColStrings.ToArray()), string.Join(",", lSetStrings.ToArray())); dbc.CommandText = DataCopy.CheckQueryByDialect(sCmdQuery); return(dbc); }
public static IDbCommand GenerateUpdateCommand(IDbConnection connection, IDbTransaction transaction, DataTable dt, TableSpecification ts) { string sTemplate = @" UPDATE [{0}] _SET_ {1} WHERE {2} "; IDbCommand dbc = SqlObjectFactory.CreateDbCommand(connection, transaction, string.Empty); dbc.CommandTimeout = 1000; List <string> lSetStrings = new List <string>(); List <string> lIdentityStrings = new List <string>(); //sIdentityName = sIdentityName.ToLowerInvariant(); foreach (DataColumn dc in dt.Columns) { if (ts.IdentityNames.Contains(dc.ColumnName.ToLowerInvariant())) { lIdentityStrings.Add(string.Format(" [{0}] = @{0}", dc.ColumnName)); } else { lSetStrings.Add(string.Format(" [{0}] = @{0}", dc.ColumnName)); } dbc.Parameters.Add(SqlObjectFactory.CreateParameter(dc.ColumnName, null, dc.ColumnName)); } string sCmdQuery = string.Format(sTemplate, ts.TableName, string.Join(",\r\n", lSetStrings.ToArray()), string.Join("\r\nAND\r\n", lIdentityStrings.ToArray())); dbc.CommandText = DataCopy.CheckQueryByDialect(sCmdQuery); return(dbc); }
public static DataTable GetDataTableWithSqlParams(IDbConnection dcn, IDbTransaction dtn, string sQuery, List <IDbDataParameter> lParams) { try { sQuery = CheckQueryByDialect(sQuery); using (IDbCommand cmd = SqlObjectFactory.CreateDbCommand(dcn, dtn, sQuery)) { cmd.CommandTimeout = 300; foreach (IDbDataParameter prm in lParams) { cmd.Parameters.Add(prm); } using (IDisposable dsp = SqlObjectFactory.CreateDbAdapter(cmd) as IDisposable) { IDbDataAdapter da = dsp as IDbDataAdapter; using (DataSet ds = new DataSet()) { da.Fill(ds); DataTable dtResult = ds.Tables[0]; ds.Tables.Clear(); return(dtResult); } } } } catch (Exception excp) { m_logger.ErrorFormat("GetDataTableWithSqlParams() ERROR:{0}\r\n{1}\r\nfor query:\r\n{2}", excp, excp.Message, excp.StackTrace, sQuery); } return(null); }
public static DataTable GetDataTable(IDbConnection dcn, IDbTransaction dtn, string sFormatQuery, params object[] args) { string sQuery = sFormatQuery; try { EnsureConnection(dcn); sQuery = string.Format(sFormatQuery, args); sQuery = CheckQueryByDialect(sQuery); using (IDbCommand cmd = SqlObjectFactory.CreateDbCommand(dcn, dtn, sQuery)) { using (IDisposable dsp = SqlObjectFactory.CreateDbAdapter(cmd) as IDisposable) { IDbDataAdapter da = dsp as IDbDataAdapter; using (DataSet ds = new DataSet()) { da.Fill(ds); DataTable dtResult = ds.Tables[0]; ds.Tables.Clear(); return(dtResult); } } } } catch (Exception excp) { m_logger.Excp(excp, "GetDataTable() ERROR:\r\nQuery:\r\n{0}\r\n", sQuery); } return(new DataTable()); }