Beispiel #1
1
        public static void SqlCmdExample()
        {
            var transaction = new SqlTrans();
            var cmd1 = new SqlCmd("UPDATE ... ");
            var cmd2 = new SqlCmd("INSERT ...");

            transaction.Add(cmd1);
            transaction.Add(cmd2);

            cmd1.ExecuteNonQuery();
            cmd2.ExecuteNonQuery();

            transaction.Commit();
        }
Beispiel #2
0
        private bool ExecuteSql(StringBuffer buffer)
        {
            if (buffer.BatchSize == 0)
            {
                return(true);
            }

            string sql = buffer.AllText;

            try
            {
                var cmd = new SqlCmd(provider, sql);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                OnError(new SqlExceptionEventArgs(sql, ex)
                {
                    Line = buffer.BatchLine
                });
                return(false);
            }

            OnReported(new SqlExecutionEventArgs(sql)
            {
                BatchLine = buffer.BatchLine,
                BatchSize = buffer.BatchSize,
                Line      = buffer.Line,
                TotalSize = buffer.TotalSize
            });;

            return(true);
        }
Beispiel #3
0
        public bool Update()
        {
            RefreshRow();

            UpdateOriginValue(this.Row1);

            if (this.Row1.EqualTo(dataRow))
            {
                return(false); //Nothing is changed
            }
            if (!OnRowChanged(ObjectState.Modified, false))
            {
                return(false);
            }

            string SQL;

            tryUpdateQuery(out SQL);
#if DEBUG
            Validate();
#endif

            SqlCmd sqlCmd = new SqlCmd(this.TableName.Provider, SQL);
            foreach (ColumnAdapter column in columns)
            {
                if (column.Field.Saved || column.Field.Identity || column.Field.Primary)
                {
                    column.AddParameter(sqlCmd);

                    if (column.IsValueChanged)
                    {
                        column.OnVauleChanged();
                    }
                }
            }

            if (this.transaction != null)
            {
                transaction.Add(sqlCmd);
            }

            sqlCmd.ExecuteNonQuery();

            //Identity Columns
            foreach (ColumnAdapter column in columns)
            {
                if (column.Field.Identity)
                {
                    dataRow[column.Field.Name] = this.Row1[column.Field.Name];
                    column.UpdateValue(dataRow);
                }
            }

            return(true);
        }
Beispiel #4
0
        public static void SqlCmdExample()
        {
            var transaction = new SqlTrans();
            var cmd1        = new SqlCmd("UPDATE ... ");
            var cmd2        = new SqlCmd("INSERT ...");

            transaction.Add(cmd1);
            transaction.Add(cmd2);

            cmd1.ExecuteNonQuery();
            cmd2.ExecuteNonQuery();

            transaction.Commit();
        }
Beispiel #5
0
        public virtual bool Delete()
        {
            DataRow r = this.Row1;

            if (!Exists)
            {
                return(false);
            }
            else
            {
                UpdateOriginValue(r);
            }

            if (!OnRowChanged(ObjectState.Deleted, false))
            {
                return(false);
            }


            SqlCmd sqlCmd = new SqlCmd(this.TableName.Provider, deleteQuery());

            foreach (ColumnAdapter column in columns)
            {
                column.AddParameter(sqlCmd);
            }

            if (this.transaction != null)
            {
                transaction.Add(sqlCmd);
            }

            sqlCmd.ExecuteNonQuery();


            Clear();
            dataRow.Delete();


            return(true);
        }
Beispiel #6
0
        public static int ExecuteNonQuery(this ConnectionProvider provider, string script)
        {
            SqlCmd cmd = new SqlCmd(provider, script);

            return(cmd.ExecuteNonQuery());
        }
Beispiel #7
0
        private void Execute()
        {
            string text = textBox.GetSelectionOrAllText();

            if (text == string.Empty)
                return;

            tabControl.Items.Clear();

            var cmd = new SqlCmd(provider, text);
            if (text.IndexOf("select", StringComparison.CurrentCultureIgnoreCase) >= 0
                && text.IndexOf("insert", StringComparison.CurrentCultureIgnoreCase) < 0
                && text.IndexOf("update", StringComparison.CurrentCultureIgnoreCase) < 0
                && text.IndexOf("delete", StringComparison.CurrentCultureIgnoreCase) < 0
                )
            {
                try
                {
                    StringBuilder builder = new StringBuilder();
                    var ds = cmd.FillDataSet();
                    int i = 1;
                    foreach (DataTable dt in ds.Tables)
                    {
                        var tab = new TabItem { Header = $"Table {i++}", Content = DisplayTable(dt) };
                        tabControl.Items.Add(tab);
                        builder.AppendLine($"{dt.Rows.Count} row(s) affected");
                    }

                    DisplayMessage(builder.ToString());
                }
                catch (SqlException ex)
                {
                    DisplayMessage(ex.Message());
                }
                catch (Exception ex)
                {
                    DisplayMessage(ex.Message);
                }

            }
            else
            {
                try
                {
                    int count = cmd.ExecuteNonQuery();
                    string message = $"{count} row(s) affected";
                    DisplayMessage(message);
                }
                catch (SqlException ex)
                {
                    DisplayMessage(ex.Message());
                }
                catch (Exception ex)
                {
                    DisplayMessage(ex.Message);
                }
            }

            if (tabControl.HasItems)
                (tabControl.Items[0] as TabItem).Focus();
        }
Beispiel #8
0
        public bool Update()
        {
            RefreshRow();

            UpdateOriginValue(this.Row1);

            if (this.Row1.EqualTo(dataRow))
                return false; //Nothing is changed

            if (!OnRowChanged(ObjectState.Modified, false))
                return false;

            string SQL;
            tryUpdateQuery(out SQL);
            #if DEBUG
            Validate();
            #endif

            SqlCmd sqlCmd = new SqlCmd(this.TableName.Provider, SQL);
            foreach (ColumnAdapter column in columns)
            {
                if (column.Field.Saved || column.Field.Identity || column.Field.Primary)
                {
                    column.AddParameter(sqlCmd);

                    if (column.IsValueChanged)
                        column.OnVauleChanged();
                }
            }

            if (this.transaction != null)
                transaction.Add(sqlCmd);

            sqlCmd.ExecuteNonQuery();

            //Identity Columns
            foreach (ColumnAdapter column in columns)
            {
                if (column.Field.Identity)
                {
                    dataRow[column.Field.Name] = this.Row1[column.Field.Name];
                    column.UpdateValue(dataRow);
                }
            }

            return true;
        }
Beispiel #9
0
        public bool Insert()
        {
            if (!OnRowChanged(ObjectState.Added, false))
                return false;

            string SQL = insertQuery();

            #if DEBUG
            Validate();
            #endif

            SqlCmd sqlCmd = new SqlCmd(this.TableName.Provider, SQL);
            foreach (ColumnAdapter column  in columns)
            {
                if (column.Field.Identity)
                {
                    if (!this.InsertIdentityOn)
                        column.AddIdentityParameter(sqlCmd);
                    else
                        column.AddParameter(sqlCmd);
                }
                else if (column.Field.Saved || column.Field.Primary)
                {
                    column.AddParameter(sqlCmd);

                    if (column.IsValueChanged)
                        column.OnVauleChanged();
                }
            }

            if (this.transaction != null)
                transaction.Add(sqlCmd);

            sqlCmd.ExecuteNonQuery();

            //Identity Columns
            if (!this.InsertIdentityOn)
            {
                bool hasIdentity = false;
                foreach (ColumnAdapter column in columns)
                {
                    if (column.Field.Identity)
                    {
                        hasIdentity = true;
                        dataRow[column.Field.Name] = sqlCmd.GetReturnValue(column.Field.ParameterName);
                        column.UpdateValue(dataRow);
                    }
                }
                if (hasIdentity)
                    OnRowChanged(ObjectState.Added, true);
            }

            return true;
        }
Beispiel #10
0
        public virtual bool Delete()
        {
            DataRow r = this.Row1;

            if (!Exists)
                return false;
            else
                UpdateOriginValue(r);

            if (!OnRowChanged(ObjectState.Deleted, false))
                return false;

            SqlCmd sqlCmd = new SqlCmd(this.TableName.Provider, deleteQuery());
            foreach (ColumnAdapter column in columns)
            {
                column.AddParameter(sqlCmd);
            }

            if (this.transaction != null)
                transaction.Add(sqlCmd);

            sqlCmd.ExecuteNonQuery();

            Clear();
            dataRow.Delete();

            return true;
        }
Beispiel #11
0
        public bool Insert()
        {
            if (!OnRowChanged(ObjectState.Added, false))
            {
                return(false);
            }

            string SQL = insertQuery();

#if DEBUG
            Validate();
#endif

            SqlCmd sqlCmd = new SqlCmd(this.TableName.Provider, SQL);
            foreach (ColumnAdapter column  in columns)
            {
                if (column.Field.Identity)
                {
                    if (!this.InsertIdentityOn)
                    {
                        column.AddIdentityParameter(sqlCmd);
                    }
                    else
                    {
                        column.AddParameter(sqlCmd);
                    }
                }
                else if (column.Field.Saved || column.Field.Primary)
                {
                    column.AddParameter(sqlCmd);

                    if (column.IsValueChanged)
                    {
                        column.OnVauleChanged();
                    }
                }
            }

            if (this.transaction != null)
            {
                transaction.Add(sqlCmd);
            }

            sqlCmd.ExecuteNonQuery();

            //Identity Columns
            if (!this.InsertIdentityOn)
            {
                bool hasIdentity = false;
                foreach (ColumnAdapter column in columns)
                {
                    if (column.Field.Identity)
                    {
                        hasIdentity = true;
                        dataRow[column.Field.Name] = sqlCmd.GetReturnValue(column.Field.ParameterName);
                        column.UpdateValue(dataRow);
                    }
                }
                if (hasIdentity)
                {
                    OnRowChanged(ObjectState.Added, true);
                }
            }

            return(true);
        }
Beispiel #12
0
        private bool ExecuteSql(int i, StringBuilder builder)
        {
            string sql = builder.ToString();

            if (string.IsNullOrEmpty(sql))
                return true;

            try
            {
                var cmd = new SqlCmd(provider, sql);
                //cmd.Error += (sender, e) => { OnError(e); };
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                OnError(new SqlExceptionEventArgs(sql, ex) { Line = i });
                return false;
            }

            OnReported(new EventArgs<int, string>(i, sql));
            return true;
        }