Category: C SHARP

C# ile SQLE Resim Kaydetmek

On By semt20In C SHARPLeave a comment
resim kaydetmek için
SQL TURU VARBINARY(MAX)

c#
BM_PERSON.IMAGEDATA = DATA TURU BYTE[]
pe_GB1_IMAGE = PICTUREEDIT DEVEXPRESS COMPONENT
set = PI.BM_PERSON.IMAGEDATA = (byte[])pe_GB1_IMAGE.EditValue;
get = pe_GB1_IMAGE.EditValue = PI.BM_PERSON.IMAGEDATA;

SQL KAYDEDERKEN DIKKAT
                if (B.IMAGEDATA != null)
                    com.Parameters.AddWithValue(“@IMAGEDATA”, B.IMAGEDATA);
                else
                    com.Parameters.AddWithValue(“@IMAGEDATA”, System.Data.SqlTypes.SqlBinary.Null);

C# SQL SERVER IN ADAPTASYONU

On By semt20In C SHARPLeave a comment 
                    string PERSONREFS = string.Empty;
                    if (F.PERSONREFS != null && F.PERSONREFS.Length > 0)
                        for (int i = 0; i < F.PERSONREFS.Length; i++)
                        {
                            PERSONREFS += "'" + F.PERSONREFS[i] + "'";
                            if (i != F.PERSONREFS.Length - 1)
                                PERSONREFS += ", ";
                        }
                    else
                        PERSONREFS = "-1";

C# INSERT COMMAND WITH SCOPE IDENTITY

On By semt20In C SHARPLeave a comment 
        private void CREATE_NEW_LIST()
        {
            SqlCommand COM = null;
            SqlTransaction TRANSACTION = null;
            SqlConnection CON = new SqlConnection(CONSTR_LG);
            if (CON.State != ConnectionState.Open)
                CON.Open();
            TRANSACTION = CON.BeginTransaction();




            COM = INSERT_INTO_BM_PNTAWARDFICHE(int.Parse(le_CAPIFIRM.EditValue.ToString()), int.Parse(textEditYIL.Text), int.Parse(comboBoxEditAY.Text));
            COM.Connection = CON;
            COM.Transaction = TRANSACTION;
             int LOGICALREF = int.Parse(COM.ExecuteScalar().ToString());
           // YUKARIDA EXECUTE SCALAR OLDUGU ICIN GEREK YOK BUNA: COM.ExecuteNonQuery();

            COM = UTILITIES.DELETE_PNTAWARDLINES(int.Parse(le_CAPIFIRM.EditValue.ToString()),  int.Parse(textEditYIL.Text), int.Parse(comboBoxEditAY.Text));
            COM.Connection = CON;
            COM.Transaction = TRANSACTION;
            COM.ExecuteNonQuery();




   TRANSACTION.Commit();
          }

        public static SqlCommand INSERT_INTO_BM_PNTAWARDFICHE(int FIRMNR, int YEAR, int MONTH)
        {
            try
            {
                return new SqlCommand("INSERT INTO BM_PNTAWARDFICHE(FIRMNR, YEAR, MONTH, FOOD, TRANSPORTATION, OBIM, KASA, FOOD_B, TRANSPORTATION_B, OBIM_B, KASA_B, CREATEDBY, CREATEDDATE, MODIFIEDBY, MODIFIEDDATE) VALUES ('" + FIRMNR + "', " + YEAR + ", " + MONTH + ", 0, 0,0, 0, 0, 0, 0, 0, 99, '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "', 0, 0) select  SCOPE_IDENTITY() ");
            }
            catch { return null; }
        }

        public static string CONSTR_LG = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};MultipleActiveResultSets=True;", BMS_DLL.CFGICERIK.LGDBSERVER, BMS_DLL.CFGICERIK.LGDBDATABASE, BMS_DLL.CFGICERIK.LGDBUSERNAME, BMS_DLL.CFGICERIK.LGDBPASSWORD);

C# excel dxgridview fast load

On By semt20In C SHARPLeave a comment 
//DXGRIDVIEW EXCEL ACMA HIZLI
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;
using Excel;
using System;
using System.Data;
using System.IO;
using System.Windows.Forms;
//F:\googledrive\programming\csharp içinde iki adet dll var  Excel.dll  ve ICSharpCode.SharpZipLib.dll referans olarak ekle




        public void SET_GC_DATASOURCE(DataTable DT, string GVNAME){
            try
            {
                if (DT == null || DT.Rows.Count <= 0)
                    throw new Exception("EXCEL VERİLERİ OKUNAMADI.");
                gridControl1.DataSource = DT;
                gridControl1.RefreshDataSource();
                gridControl1.Refresh();
                INITIALIZE_GRID(gridControl1, gridView1, new GRIDOPTIONS() { ALLOWSORT = true, CLEARSORTINFO = true, ENABLEGROUPPANELMENU = true, HORIZONTALSCROOLVISIBILITY = true, SHOWGROUPPANEL = true }, GVNAME);
                //for (int i = 0; i < COLUMNS.Length / 2; i++)
                //{
                //    _gv_EKSTRE.Columns[i].Caption = (string)COLUMNS[i, 0];
                //    _gv_EKSTRE.Columns[i].Visible = (bool)COLUMNS[i, 1];
                //}

            }
            catch (Exception E)
            {

                MessageBox.Show(E.Message, "HATA!", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
        }



    public string READ_FILE(string title){
            try
            {
                string[] fileformats = new string[] { "xls", "xlsx" };
                OpenFileDialog openFileDialogEKSTRE = new OpenFileDialog();
                openFileDialogEKSTRE.Title = title;
                //openFileDialogEKSTRE.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);//@"C:\Users\BilMark Yazılım\Desktop";//AppDomain.CurrentDomain.BaseDirectory;
                string filterText = string.Empty;
                for (int i = 0; i < fileformats.Length; i++)
                {
                    filterText += "(*." + fileformats[i] + "*)|*." + fileformats[i];
                    if (i != fileformats.Length - 1)
                        filterText += "|";
                }
                openFileDialogEKSTRE.Filter = "Excel Files|*.xls;*.xlsx;";
                //openFileDialogEKSTRE.FileName = "1001.xlsx";
                openFileDialogEKSTRE.FilterIndex = 0;
                openFileDialogEKSTRE.RestoreDirectory = true;

                if (openFileDialogEKSTRE.ShowDialog() == DialogResult.OK)
                {
                    return openFileDialogEKSTRE.FileName;
                }
                else return "closed";
            }
            catch (Exception E)
            {

                return null;
            }
        }





      public DataTable CONVERT_EXCEL_TO_DATATABLE(bool ISXLSX, bool ISFIRSTROWCOLUMNNAME, string PATH, int RETURNEDTABLENR){
            FileStream stream = File.Open(PATH, FileMode.Open, FileAccess.Read);
            IExcelDataReader excelReader = null;

            if (ISXLSX)
                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            else
                excelReader = ExcelReaderFactory.CreateBinaryReader(stream);

            excelReader.IsFirstRowAsColumnNames = ISFIRSTROWCOLUMNNAME;

            DataSet result = excelReader.AsDataSet();

            ////5. Data Reader methods
            //while (excelReader.Read())
            //{
            //    //excelReader.GetInt32(0);
            //}

            excelReader.Close();

            return result.Tables[RETURNEDTABLENR];}





       public void INITIALIZE_GRID(GridControl GC, GridView GV, GRIDOPTIONS GO, string XMLLAYOUTNAME){

            try
            {
                RESTORE_GRIDVIEW_LAYOUTS(GV, XMLLAYOUTNAME);
                //GV.RestoreLayoutFromXml(AppDomain.CurrentDomain.BaseDirectory + "\\Layouts\\" + XMLLAYOUTNAME + ".xml");
                if (GO.CLEARSORTINFO)
                    GV.SortInfo.Clear();
                GV.ClearColumnsFilter();
                GV.FocusedRowHandle = 0;
            }
            catch { }

            GV.OptionsBehavior.Editable = GO.EDITABLE;
            GV.OptionsBehavior.AllowAddRows = GO.ALLOWADDROW ? DevExpress.Utils.DefaultBoolean.True : DevExpress.Utils.DefaultBoolean.False;
            GV.OptionsBehavior.AllowDeleteRows = GO.ALLOWDELETEROW ? DevExpress.Utils.DefaultBoolean.True : DevExpress.Utils.DefaultBoolean.False;

            GV.OptionsCustomization.AllowFilter = GO.ALLOWFILTER;
            GV.OptionsCustomization.AllowQuickHideColumns = GO.ALLOWQUICKHIDECOLUMNS;
            GV.OptionsCustomization.AllowSort = GO.ALLOWSORT;

            GV.OptionsMenu.EnableColumnMenu = GO.ENABLECOLUMNMENU;
            GV.OptionsMenu.EnableFooterMenu = GO.ENABLEFOOTERMENU;
            GV.OptionsMenu.EnableGroupPanelMenu = GO.ENABLEGROUPPANELMENU;

            GV.OptionsSelection.MultiSelect = GO.MULTISELECT;
            if (GO.MULTISELECTMODECELLSELECT)
                GV.OptionsSelection.MultiSelectMode = GridMultiSelectMode.CellSelect;
            else if (GO.MULTISELECTMODECHECKBOXSELECT)
                GV.OptionsSelection.MultiSelectMode = GridMultiSelectMode.CheckBoxRowSelect;
            else if (GO.MULTISELECTMODEWORSELECT)
                GV.OptionsSelection.MultiSelectMode = GridMultiSelectMode.RowSelect;

            GV.OptionsView.ColumnAutoWidth = GO.COLUMNAUTOWIDTH;
            GV.OptionsView.ShowAutoFilterRow = GO.SHOWAUTOFILTERROW;
            GV.OptionsView.ShowGroupPanel = GO.SHOWGROUPPANEL;
            GV.OptionsView.ShowFilterPanelMode = GO.SHOWFILTERPANELMODE ? DevExpress.XtraGrid.Views.Base.ShowFilterPanelMode.ShowAlways : DevExpress.XtraGrid.Views.Base.ShowFilterPanelMode.Default;}





     public void RESTORE_GRIDVIEW_LAYOUTS(GridView GV, string XMLLAYOUTNAME){
            try
            {
                GV.RestoreLayoutFromXml(AppDomain.CurrentDomain.BaseDirectory + "\\Layouts\\" + XMLLAYOUTNAME + ".xml");
            }
            catch { }}




public class GRIDOPTIONS{
        #region OPTIONSBEHAVIOR
        public bool EDITABLE { get; set; }
        public bool ALLOWADDROW { get; set; }
        public bool ALLOWDELETEROW { get; set; }
        #endregion

        #region OPTIONSCUSTOMIZATION
        public bool ALLOWFILTER { get; set; }
        public bool ALLOWSORT { get; set; }
        public bool ALLOWQUICKHIDECOLUMNS { get; set; }
        #endregion

        #region OPTIONSMENU
        public bool ENABLECOLUMNMENU { get; set; }
        public bool ENABLEGROUPPANELMENU { get; set; }
        public bool ENABLEFOOTERMENU { get; set; }
        #endregion

        #region OPTIONSSELECTION
        public bool MULTISELECT { get; set; }
        public bool MULTISELECTMODECELLSELECT { get; set; }
        public bool MULTISELECTMODECHECKBOXSELECT { get; set; }
        public bool MULTISELECTMODEWORSELECT { get; set; }
        #endregion

        #region OPTIONSVIEW
        public bool COLUMNAUTOWIDTH { get; set; }
        public bool SHOWGROUPPANEL { get; set; }
        public bool SHOWAUTOFILTERROW { get; set; }
        public bool SHOWFILTERPANELMODE { get; set; }
        #endregion

        #region BEHAVIOR
        public bool HORIZONTALSCROOLVISIBILITY { get; set; }
        #endregion

        public bool CLEARSORTINFO { get; set; }
        public bool CLEARFILTERINFO { get; set; }}




        private void simpleButton1_Click(object sender, EventArgs e){
            gridControl1.DataSource = null;
            gridView1.Columns.Clear();
            string  FILEPATH = READ_FILE("gridView1");
            SET_GC_DATASOURCE(CONVERT_EXCEL_TO_DATATABLE(true, true, FILEPATH, 0), "gridView1");}

c# devexpress gridview selectedrow to datatable dataset

On By semt20In C SHARPLeave a comment 
            DataTable MyTable = new DataTable(); // 1
            DataTable MyTableByName = new DataTable("MyTableName"); // 2




            MyTable.Columns.Add("AMBAR", typeof(string));

            int[] SROWSdublicate = _gv_SARFFISI_.GetSelectedRows();
            for (int i = 0; i < SROWSdublicate.Length; i++)
            {
                MyTable.Rows.Add(_gv_SARFFISI_.GetRowCellValue(SROWSdublicate[i], "Ambar").ToString());
                //dr["AMBAR"] = _gv_SARFFISI_.GetRowCellValue(SROWSdublicate[i], "Ambar").ToString();
                //dt.Rows.Add(new object[] { "Ravi", 500 });
                // dt.Rows.Add(dr);

            }              

whole grid rows (without select)

for (int i = 0; i < gridView1.RowCount; i++) {

datatable to list convert

On By semt20In C SHARPLeave a comment 
   public class MOBILEUSER
    {
        public int LOGICALREF { get; set; }
        public int CAPIUSERREF { get; set; }
        public string NAME { get; set; } = string.Empty;
        public string KEY_ { get; set; } = string.Empty;
        public string DEFINITION_ { get; set; }
        public int SLSMANREF { get; set; }
        public string SLSMANCODE { get; set; } = string.Empty;
        public string FCMREGID { get; set; } = string.Empty;
        public int CONFTYPE { get; set; }
        public double CONF_SAMOUNT { get; set; }
        public double CONF_FAMOUNT { get; set; }
        public double CONF_SPERCENT { get; set; }
        public double CONF_FPERCENT { get; set; }
        public short ACTIVE { get; set; }
        public string NTF_ORFICHEREF { get; set; } = string.Empty;
        public string NTF_ORFICHENO { get; set; } = string.Empty;
    }   




   private List GET_MOBILEUSER_LIST()
        {
            List MUL = new List();
            try
            {
                DataTable DT = sqlLGDB.ExecuteDataSet(new SqlCommand(string.Format("SELECT * FROM BM_MOBILEUSER WHERE ACTIVE = 0"))).Tables[0];
                foreach (DataRow R in DT.Rows)
                {
                    MOBILEUSER U = new MOBILEUSER();
                    try { U.LOGICALREF = (int)R["LOGICALREF"]; } catch { }
                    try { U.CAPIUSERREF = (int)R["CAPIUSERREF"]; } catch { }
                    try { U.NAME = (string)R["NAME"]; } catch { }
                    try { U.KEY_ = (string)R["KEY_"]; } catch { }
                    try { U.DEFINITION_ = (string)R["DEFINITION_"]; } catch { }
                    try { U.SLSMANREF = (int)R["SLSMANREF"]; } catch { }
                    try { U.SLSMANCODE = (string)R["SLSMANCODE"]; } catch { }
                    try { U.FCMREGID = (string)R["FCMREGID"]; } catch { }
                    try { U.CONFTYPE = (int)R["CONFTYPE"]; } catch { }
                    try { U.CONF_SAMOUNT = (double)R["CONF_SAMOUNT"]; } catch { }
                    try { U.CONF_FAMOUNT = (double)R["CONF_FAMOUNT"]; } catch { }
                    try { U.CONF_SPERCENT = (double)R["CONF_SPERCENT"]; } catch { }
                    try { U.CONF_FPERCENT = (double)R["CONF_FPERCENT"]; } catch { }
                    try { U.ACTIVE = (short)R["ACTIVE"]; } catch { }
                    MUL.Add(U);
                }

            }
            catch (Exception E)
            {
                WRITELOG("GET_MOBILEUSER_LIST", E, false);
            }
            return MUL;
        }