Пример #1
0
        private void btnGenerate_Click(object sender, EventArgs e)
        {
            DataRowView row = this.bindsrc.Current as DataRowView;

            if (row == null)
            {
                MessageBox.Show("当前没有选择任何的表,无法生成。", "警告", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }
            string  tblName = row["TblName"].ToString();
            string  strSQL  = @"
	SELECT clmns.name AS [ColName]
	FROM sys.tables AS tbl
		INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
	WHERE tbl.name = '"     + tblName + @"' AND SCHEMA_NAME(tbl.schema_id)=N'dbo'
	ORDER BY clmns.column_id ASC"    ;
            DataSet ds      = SqlDAL.ExecuteQuery(strSQL);

            if (this.rbtnSelect.Checked)
            {
                this.txtResult.Text = this.getSelectSQL(ds.Tables[0], tblName);
            }
            else if (this.rbtnInsert.Checked)
            {
                this.txtResult.Text = this.getInsertSQL(ds.Tables[0], tblName);
            }
            else
            {
                this.txtResult.Text = this.getUpdateSQL(ds.Tables[0], tblName);
            }
        }
Пример #2
0
        private void btBackupDB_Click(object sender, EventArgs e)
        {
            string strSQL = "BACKUP DATABASE " + currentDbName + " TO DISK= '"
                            + getBakFileName() + "' WITH INIT";

            SqlDAL.ExecuteCmd(strSQL);
            MessageBox.Show("Backup database successed!", "information", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
        }
Пример #3
0
        private string getBakFileName()
        {
            string    strSQL = "SELECT TOP 1 [filename] FROM [sysfiles]";
            DataTable tbl    = SqlDAL.ExecuteQuery(strSQL).Tables[0];

            if (tbl == null || tbl.Rows.Count <= 0)
            {
                MessageBox.Show("Unkowns database path name. Backup failed!", "Notice", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return("");
            }
            return(tbl.Rows[0][0].ToString().Replace(".mdf", DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".bak"));
        }
Пример #4
0
        private string getUpdateSQL(DataTable tblColumns, string strTable)
        {
            // 1 取得主键
            string        strKey   = @"
SELECT ind.name, dsp.name as space_name, col.name as column_nName, ind_col.key_ordinal, ind_col.column_id
FROM sys.indexes ind 
LEFT OUTER JOIN sys.stats sta ON sta.object_id = ind.object_id and sta.stats_id = ind.index_id 
LEFT OUTER JOIN 
	(
		sys.index_columns ind_col INNER JOIN sys.columns col 
		ON col.object_id = ind_col.object_id and col.column_id = ind_col.column_id
	)ON ind_col.object_id = ind.object_id and ind_col.index_id = ind.index_id 
	LEFT OUTER JOIN sys.data_spaces dsp ON dsp.data_space_id = ind.data_space_id  
WHERE ind.object_id = object_id(N'" + strTable + @"')  AND ind.index_id >= 0 AND ind.type <> 3 AND ind.is_hypothetical = 0   
ORDER BY ind.index_id, ind_col.key_ordinal";
            DataTable     keyTable = SqlDAL.ExecuteQuery(strKey).Tables[0];
            List <string> lstKey   = new List <string>();

            foreach (DataRow r in keyTable.Rows)
            {
                lstKey.Add(r["column_nName"].ToString());
            }

            string strResult = "UPDATE dbo." + strTable + " SET ";

            foreach (DataRow r in tblColumns.Rows)
            {
                // 如果该字段不是key field
                if (!lstKey.Contains(r[0].ToString()))
                {
                    //03 判断是否转换小写
                    if (this.chkLower.Checked)
                    {
                        strResult += r[0].ToString().ToLower() + " = @" + r[0].ToString().ToLower() + ", ";
                    }
                    else
                    {
                        strResult += r[0].ToString() + " = @" + r[0].ToString() + ", ";
                    }
                }
            }
            strResult = strResult.Remove(strResult.Length - 2) + Environment.NewLine + "WHERE ";
            foreach (string r in lstKey)
            {
                strResult += r + " = @" + r + " AND ";
            }
            return(strResult.Remove(strResult.Length - 4));
        }
Пример #5
0
        private void fillTablesList()
        {
            string  strSQL = @"
SELECT tbl.name AS [TblName]--,SCHEMA_NAME(tbl.schema_id) AS [Schema], tbl.create_date AS [CreateDate]
FROM sys.tables AS tbl
WHERE
	(CAST(
		case 
		when tbl.is_ms_shipped = 1 then 1
		when ( select major_id from sys.extended_properties  
			   where major_id = tbl.object_id and minor_id = 0 and class = 1 and name = N'microsoft_database_tools_support') 
			is not null then 1 else 0
		end 
	AS bit)=0)
ORDER BY [TblName] ASC";
            DataSet ds     = SqlDAL.ExecuteQuery(strSQL);

            this.bindsrc.DataSource    = ds.Tables[0];
            this.gridTables.DataSource = this.bindsrc;
        }
Пример #6
0
        private void fillDbList()
        {
            string  strSQL = @"
SELECT
	dtb.name AS [Database_Name],
	dtb.name AS [Database_DatabaseName2]
FROM master.sys.databases AS dtb
	LEFT OUTER JOIN sys.database_mirroring AS dmi ON dmi.database_id = dtb.database_id
WHERE
	(    CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit)=0 
	 AND CAST(isnull(dtb.source_database_id, 0) AS bit)=0)
ORDER BY [Database_Name] ASC";
            DataSet ds     = SqlDAL.ExecuteQuery(strSQL);

            this.cmbDb.Items.Clear();
            this.cmbDb.SelectedIndex = -1;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                this.cmbDb.Items.Add(r[0]);
            }
        }