Пример #1
0
        public static int Delete(string tablename, string username, List <KeyValuePair <string, int> > primaryKey)
        {
            // TODO: we could use an enumerator here. seems like a waste to make a copy of a collection
            IEnumerable <string> keys = ExtractKeys <string, int>(primaryKey);

            string sql = SqlBuilder.BuildParameterizedDeleteStatement(tablename, keys);

            SqlCommand command = new SqlCommand(sql);

            foreach (KeyValuePair <string, int> kvp in primaryKey)
            {
                SqlCommandUtils.AddInputParam(command, kvp.Key, kvp.Value);
            }

            SqlCommand dcmd;

            if (primaryKey.Count > 1)
            {
                dcmd = BuildAuditDeleteCommand(tablename, username, primaryKey[0].Key, primaryKey[0].Value.ToString(),
                                               primaryKey[1].Key, primaryKey[1].Value.ToString());
            }
            else
            {
                dcmd = BuildAuditDeleteCommand(tablename, username, primaryKey[0].Key, primaryKey[0].Value.ToString(), null, null);
            }

            return(Delete(command, dcmd));
        }
Пример #2
0
        public static int GetByField(string tablename, string fieldname, object fieldvalue, string primaryKeyName, IEnumerable <string> virtualFields, DataTable table)
        {
            SqlCommand command = new SqlCommand(SqlBuilder.BuildVirtualParameterizedSelectStatement(tablename, primaryKeyName, fieldname, virtualFields));

            SqlCommandUtils.AddInputParam(command, fieldname, fieldvalue);

            return(Get(command, table));
        }
Пример #3
0
        public static int GetByField(string tablename, string fieldname, object fieldvalue, DataTable table)
        {
            string sql = SqlBuilder.BuildParameterizedSelectByStatement(tablename, fieldname);

            SqlCommand command = new SqlCommand(sql);

            SqlCommandUtils.AddInputParam(command, fieldname, fieldvalue);

            return(Get(command, table));
        }
Пример #4
0
        // uses new QueryBuilder
        public static int Get(QueryBuilder query, DataTable table)
        {
            SqlCommand command = new SqlCommand(query.ToSql());

            foreach (string k in query.Parameters.Keys)
            {
                SqlCommandUtils.AddInputParam(command, k, query.Parameters[k]);
            }

            return(Get(command, table));
        }
Пример #5
0
        private static T Crud <T>(string tablename, List <KeyValuePair <string, int> > primaryKey,
                                  DataTable table, CrudDelegate f, ExecutionDelegate <T> execsql)
        {
            SqlCommand cmd = new SqlCommand(f(table, ExtractKeys <string, int>(primaryKey)));

            foreach (KeyValuePair <string, int> kvp in primaryKey)
            {
                SqlCommandUtils.AddInputParam(cmd, kvp.Key, kvp.Value);
            }

            return(execsql(cmd, table));
        }
Пример #6
0
        public static int Get(string tablename, List <KeyValuePair <string, int> > primaryKey, DataTable table)
        {
            IEnumerable <string> keys = ExtractKeys <string, int>(primaryKey);

            // our various CRUD statements differ only via this method call (all share the same signature?)
            string sql = SqlBuilder.BuildParameterizedSelectByStatement(tablename, keys);

            SqlCommand command = new SqlCommand(sql);

            foreach (KeyValuePair <string, int> kvp in primaryKey)
            {
                SqlCommandUtils.AddInputParam(command, kvp.Key, kvp.Value);
            }

            return(Get(command, table));
        }
Пример #7
0
        // reuse the connection of other delete commands
        private static SqlCommand BuildAuditDeleteCommand(string tablename, string username, string pkname1, string pkvalue1,
                                                          string pkname2, string pkvalue2)
        {
            SqlCommand cmd = new SqlCommand("spUpdateAuditLogDeletedBy");

            cmd.CommandType = CommandType.StoredProcedure;

            // TableName, UserName, PKColumnName1, PKColumnValue1, PKColumnName2?, PKColumnValue2?
            SqlCommandUtils.AddInputParam(cmd, "TableName", tablename);
            SqlCommandUtils.AddInputParam(cmd, "UserName", username);
            SqlCommandUtils.AddInputParam(cmd, "PKColumnName1", pkname1);
            SqlCommandUtils.AddInputParam(cmd, "PKColumnValue1", pkvalue1);

            if (pkname2 != null && pkname2 != "" && pkvalue2 != null && pkvalue2 != "")
            {
                SqlCommandUtils.AddInputParam(cmd, "PKColumnName2", pkname2);
                SqlCommandUtils.AddInputParam(cmd, "PKColumnValue2", pkvalue2);
            }

            return(cmd);
        }