예제 #1
0
        public bool DeleteData(Dictionary <string, string> selectedRow, Table table)
        {
            StringBuilder whereFields = new StringBuilder();

            var lst = table.lstColumnNames.ToList();

            if (string.IsNullOrEmpty(table.primaryKey))
            {
                for (int i = 0; i < lst.Count; i++)
                {
                    whereFields.Append(lst[i] + "=@param" + i + " ");
                    if (i != lst.Count - 1)
                    {
                        whereFields.Append(" AND ");
                    }
                }
            }
            else
            {
                whereFields.Append(table.primaryKey + "=@param0");
            }
            string query = "DELETE FROM " + table.tableName + " WHERE " + whereFields;

            if (connection.GetState() == ConnectionState.Open)
            {
                connection.Close();
            }
            connection.Open();
            var s = "";

            try
            {
                SQLCommandInterface sqlCommand = connection.CreateCommand();
                sqlCommand.AddQuery(query);
                for (int i = 0; i < lst.Count; i++)
                {
                    sqlCommand.AddParameter("@param" + i, selectedRow[lst[i]].ToString());
                }
                s = sqlCommand.GetCommandText();
                var result = sqlCommand.ExecuteNonQuery();
                connection.Close();
                if (result != 0)
                {
                    return(true);
                }
                return(false);
            }
            catch (Exception e)
            {
                connection.Close();
                return(false);
            }

            return(true);
        }
예제 #2
0
        public bool UpdateData(Dictionary <string, object> values, Table table, Dictionary <string, object> oldValues)
        {
            StringBuilder setFields      = new StringBuilder();
            StringBuilder whereFields    = new StringBuilder();
            var           lst            = table.lstColumnNames.Where(x => x != table.AutoIncrementColumnNames).ToList();
            var           havePrimaryKey = string.IsNullOrEmpty(table.primaryKey);

            for (int i = 0; i < lst.Count; i++)
            {
                setFields.Append(lst[i] + "=@parama" + i + " ");
                if (havePrimaryKey)
                {
                    whereFields.Append(lst[i] + "=@paramb" + i + " ");
                }
                if (i != lst.Count - 1)
                {
                    setFields.Append(" , ");
                    if (havePrimaryKey)
                    {
                        whereFields.Append(" and ");
                    }
                }
                else
                {
                    if (!havePrimaryKey)
                    {
                        whereFields.Append(table.primaryKey + "=@paramb");
                    }
                }
            }
            string query = "update " + table.tableName + " set " + setFields + " where " + whereFields;

            if (connection.GetState() == ConnectionState.Open)
            {
                connection.Close();
            }
            connection.Open();
            var s = "";

            try
            {
                SQLCommandInterface sqlCommand = connection.CreateCommand();
                sqlCommand.AddQuery(query);
                var count = 0;
                for (int i = 0; i < lst.Count; i++)
                {
                    if (table.typeOfColumns[lst[i]] == "datetime" || table.typeOfColumns[lst[i]] == "date")
                    {
                        var date     = values[lst[i]].ToString().Split('/');
                        var date1    = date[2].Split(' ');
                        var datetemp = date1[0] + "/" + date[0] + "/" + date[1];
                        values[lst[i]] = datetemp;
                    }
                    sqlCommand.AddParameter("@parama" + i, values[lst[i]].ToString());
                    //sqlCommand.Parameters.AddWithValue("@parama" + i, values[lst[i]].ToString());
                    if (!havePrimaryKey && count == 0)
                    {
                        count++;
                        sqlCommand.AddParameter("@paramb", oldValues[table.primaryKey].ToString());
                        //sqlCommand.Parameters.AddWithValue("@paramb", oldValues[table.primaryKey].ToString());
                    }
                    else
                    {
                        sqlCommand.AddParameter("@paramb" + i, oldValues[lst[i]].ToString());
                        //sqlCommand.Parameters.AddWithValue("@paramb" + i, oldValues[lst[i]].ToString());
                    }
                }
                s = sqlCommand.GetCommandText();
                var result = sqlCommand.ExecuteNonQuery();
                connection.Close();
                if (result != 0)
                {
                    return(true);
                }
                return(false);
            }
            catch (Exception e)
            {
                connection.Close();
                return(false);
            }

            return(true);
        }
예제 #3
0
        public bool InsertData(Dictionary <string, string> values, Table table)
        {
            StringBuilder columnFields = new StringBuilder();
            StringBuilder valuesFields = new StringBuilder();

            columnFields.Append("(");
            valuesFields.Append("(");
            var lstColumn = table.lstColumnNames.Where(x => x != table.AutoIncrementColumnNames).ToList();

            for (int i = 0; i < lstColumn.Count; i++)
            {
                columnFields.Append(lstColumn[i]);
                valuesFields.Append("@param" + i);
                if (i < lstColumn.Count - 1)
                {
                    columnFields.Append(",");
                    valuesFields.Append(",");
                }
            }
            columnFields.Append(")");
            valuesFields.Append(")");
            string query = "Insert into" + " " + table.tableName + " " + columnFields
                           + " " + "values" + " " + valuesFields;

            if (connection.GetState() == ConnectionState.Open)
            {
                connection.Close();
            }
            connection.Open();
            try
            {
                SQLCommandInterface sqlCommand = connection.CreateCommand();
                sqlCommand.AddQuery(query);
                for (int i = 0; i < lstColumn.Count; i++)
                {
                    if (table.typeOfColumns[lstColumn[i]] == "datetime" || table.typeOfColumns[lstColumn[i]] == "date")
                    {
                        var date     = values[lstColumn[i]].ToString().Split('/');
                        var date1    = date[2].Split(' ');
                        var datetemp = date1[0] + "/" + date[0] + "/" + date[1];
                        values[lstColumn[i]] = datetemp;
                    }
                    //sqlCommand.Parameters.AddWithValue("@param" + i, values[lstColumn[i]]);
                    sqlCommand.AddParameter("@param" + i, values[lstColumn[i]]);
                }
                var result = sqlCommand.ExecuteNonQuery();
                connection.Close();
                if (result != 0)
                {
                    return(true);
                }
                return(false);
            }
            catch
            {
                connection.Close();
                return(false);
            }

            return(true);
        }